Query to join two tables by mapping third table without returning all records from third table in Oracle

Question

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.

Answer 1

You need rows, where student's ID is present in at least one of tables MATHS, ENGLISH. These queries gives output you wanted:

select id, s.name, m.Marks1, e.Marks2 
  from maths m 
    full join english e using (id)
    join student s using (id);

...or:

select s.id, s.name, m.Marks1, e.Marks2 
  from student s
    left join english e on e.id=s.id
    left join maths m on m.id = s.id
  where e.id is not null or m.id is not null

^{SQLFiddle demo}

In first query order of joining tables is important - this is why you got incorrect output. More informations with examples about joins.

Answer 2

Just change your FULL JOIN (full outer join - returns records when there is a row in either table) to a JOIN (inner join - only join when the row exists in both tables)