I have 3 tables:
Table Maths
:
ID Marks1
(1 , 80)
Table English
:
ID Marks2
(2 , 85)
Table Student
:
ID Name
(1 , Raj)
(2 , Sam)
(3 , John)
I need a SQL query (Oracle) to get the following output:
ID Name Marks1 Marks2
(1 , Raj , 80 , (null))
(2 , Sam , (null) , 85)
Some query I wrote:
select B.ID, B.Name, A.Marks1, C.Marks2 from
(select ID, Marks1 from Maths) A
full join Student B on B.ID=A.ID
left join (select ID, Marks2 from English) C
on C.ID=B.ID
But this query returns all records from Student table which I don't need. I need only records from Maths and English tables. Please help me on this.
You need rows, where student's ID is present in at least one of tables
MATHS
,ENGLISH
. These queries gives output you wanted:...or:
SQLFiddle demo
In first query order of joining tables is important - this is why you got incorrect output. More informations with examples about joins.