CODEWITHSUNDEEP
mysqljoin
Hasitha Shan
Hasitha Shan

Reputation: 2980

Join multiple tables to display data of 3 tables or more

I have three tables as follows,

Table1-
num |info
----------
1   |aaaaa  
2   |bbbb
3   |ccc
3   |ccc
4   |dddd

Table2-
num |info
----------
1   |aaaaa
3   |ccc
4   |dddd
5   |eeee
6   |ffff

Table3-
num |info
----------
1   |aaaaa
6   |ffff
2   |bbbb

the result i want to obtain is,

Result-
num |info   num |info   num |info
----------- ----------- ----------
1   |aaaaa  1   |aaaaa  1   |aaaaa
2   |bbbb   NULL|NULL   2   |bbbb
3   |ccc    3   |ccc    NULL|NULL
3   |ccc    3   |ccc    NULL|NULL
4   |dddd   4   |dddd   NULL|NULL
NULL|NULL   6   |ffff   6   |ffff

OR

Result 2-
num |info   num |info   num |info
----------- ----------- ----------
1   |aaaaa  1   |aaaaa  1   |aaaaa
2   |bbbb   NULL|NULL   2   |bbbb
3   |ccc    3   |ccc    NULL|NULL
3   |ccc    3   |ccc    NULL|NULL
4   |dddd   4   |dddd   NULL|NULL
NULL|NULL   5   |eeee   NULL|NULL
NULL|NULL   6   |ffff   6   |ffff

If there is a way to retrieve either one of the given results would be great :) Thank you very much :) .. i tried using a full join like so, 

    SELECT *
    FROM tbl_1 AS a
    LEFT OUTER JOIN tbl_2 AS b ON a.num = b.num
    UNION
    SELECT *
    FROM tbl_1 AS a
    RIGHT OUTER JOIN tbl_2 AS b ON a.num = b.num

But its good for only 2 tables, if full join is the solution how to perform this?? if not how can i derive the result i want from the given three tables??

I tried searching but none helped to perform this task.

and i asked the question Join multiple tables (Special condition) but it didnt help..thought it might be unclear thats why i posted again :)

Upvotes: 2

Views: 160

Answers (2)

Hasitha Shan
Hasitha Shan

Reputation: 2980

This question was resolved by @Andreas Wederbrand,

Full join for 3 tables

Implementing a full join was the solution for my query.

Hope it helps for anyone who comes across this issue :)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

I think as posted in your earlier question below should work well:-

  SELECT  a.num numA, a.info infoA,
    b.num numB, b.info infoB,
    c.num numC, c.info infoC
 FROM    table1 a
    LEFT JOIN table2 b
       ON a.num = b.num
    LEFT JOIN table3 c
       ON a.num = c.num
 ORDER BY a.num

Upvotes: 1

Related Questions