Join Three Tables

Can you join three tables on the same key if that key could be missing from one or more of the tables?

Here's a version that demonstrates a fully three way full outer join. A successful query will return seven rows:

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    1 |  |    2 |  |    4 |     |    1 | null | null |
|    3 |  |    3 |  |    5 | --> | null |    2 | null |
|    5 |  |    6 |  |    6 |     |    3 |    3 | null |
|    7 |  |    7 |  |    7 |     | null | null |    4 |
+------+  +------+  +------+     |    5 | null |    5 |
                                 | null |    6 |    6 |
                                 |    7 |    7 |    7 |
                                 +------+------+------+

select ColA, ColB, ColC
  from TableA
  full join TableB
    on ColA=ColB
  full join TableC
    on ColC=coalesce(ColA, ColB)
order by coalesce(ColA, ColB, ColC);

The secret is to coalesce the prior table keys in the join condition of each additional table.

You can see it in action here

Combine three tables with join an two with union

You need a join with subquery for union based on id

Select 
    t1.id, t1.naam, t2.lastname, t.Function 
From 
    t1
Left Join 
    t2 On t2.t1 - id = t1.[id]
Left Join 
    t3 On t3.t2 - id = t2.[id]
Inner Join 
    (Select id, Function
     From t2
     Union 
     Select id, Function
     From t3) t On t.id = t1.id 

JOIN three tables

try something like this

SELECT A.* FROM TableA A
LEFT OUTER JOIN TableB B ON (A.id = B.a_id)
LEFT OUTER JOIN TableC C ON (A.id = C.a_id)
WHERE B.a_id IS NOT NULL 
  OR c.a_id IS NOT NULL

SQL Inner-join with 3 tables?

You can do the following (I guessed on table fields,etc)

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

Based on your request for multiple halls you could do it this way. You just join on your Hall table multiple times for each room pref id:

SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID

Join three tables with MAX function

Use the MAX function and GROUP BY your other selections.

SELECT st.student_id, st.student_name, MAX(su.subject_id) AS subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)
GROUP BY st.student_id, st.student_name, sc.score

Output:

student_id  student_name  subject_id  score
1           Sharukh       300         20
2           Amir          300         10

SQL Fiddle: http://sqlfiddle.com/#!9/71c46a/7/0

Without the GROUP BY

SELECT st.student_id, st.student_name, su.subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)

Join three tables on condition

We can do LEFT JOIN on B and C tables separately using id. Now, we can utilize COALESCE(), to consider B.bedrooms first (if not null), else C.bedrooms. Now, there is a chance that both can be null (that is, id does not exist in either of the tables).

In that case, if you don't want those rows, you can put an additional WHERE condition to consider only those id(s), which has atleast one matching row in either B or C.

Here is one way:

select A.id,
       A.price,
       COALESCE(B.bedrooms, C.bedrooms) AS bedrooms 
from A 
left join B on A.id = B.id
left join C on A.id = C.id
WHERE NOT (B.bedrooms IS NULL AND 
           C.bedrooms IS NULL)
order by A.id

Join three tables A, B, C and return common in A in mysql

I understand that you want rows from a whose id can be foud in either b or c.

This sounds like two exists subquery:

select a.*
from a
where 
    exists (select 1 from b where b.id = a.id)
    or exists (select 1 from c where c.id = a.id)

If you also want columns from tables b or c, you can use two left joins instead, with a where condition that ensures that at least one of the joins did succeed:

select a.*, b.org, c.dept
from a
left join b on b.id = a.id
left join c on c.id = a.id
where b.id is not null or c.id is not null

How to join three tables and set blank fields to null?

A CROSS JOIN produces all the combinations you want. Then a simple outer join can retrieve the related rows (should they exist).

You don't mention the database you are using so a faily standard query will do. For example (in PostgreSQL):

select
  row_number() over(order by p.id, t.id) as id,
  p.name,
  case when x.st is not null then t.hr end,
  x.st
from person p
cross join tasks t
left join task x on x.personid_fk = p.id and x.taskid_fk = t.id
order by p.id, t.id;

Result:

 id  name  case   st    
 --- ----- ------ ----- 
 1   Anna  null   null  
 2   Anna  null   null  
 3   Luo   13:00  true  
 4   Luo   14:00  false 
 5   John  null   null  
 6   John  null   null  

See running example at DB Fiddle.

---

Related Topics