CODEWITHSUNDEEP
mysqljoin
Hasitha Shan
Hasitha Shan

Reputation: 2980

Join multiple tables (Special condition)

I was trying to think of a logic to implement this mysql query but i couldn't think of anything or find anything on the internet. My task is to Join two or more tables and return it's matching fields. BUT if there are three tables matching rows on two tables has to be displays also. Say I have these 3 tables,

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

Table2-
num |info
----------
2   |bbbb
3   |ccc
4   |dddd
5   |eeee

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

I want to join these three tables and get a result as follows,

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

I have this query which only shows that is on all three tables. 

   SELECT a . * , b. * , c . *
   FROM tbl_1 a
   JOIN tbl_2 b ON a.num = b.num
   JOIN tbl_3 c ON a.num = c.num
   ORDER BY a.num, b.num

BUT what i want, is to display even if the value appears in two tables.

Thank you very much. if this was addressed before it would be great help in pointing me in the right direction :) thanks again

TABLE STRUCTURE UPDATE

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

in such a senario the record 6 |ffff doesnt get displayd,

DESIRED OUTPUT UPDATE

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

Upvotes: 0

Views: 1528

Answers (3)

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

John Woo
John Woo

Reputation: 263743

SELECT  a.*
FROM    table1 a
        INNER JOIN
        (
            SELECT num, info FROM table2
            UNION
            SELECT num, info FROM table3
        ) b ON a.num = b.NUM

SQLFiddle Demo

UPDATE 1

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

SQLFiddle Demo

Upvotes: 4

Arun Killu
Arun Killu

Reputation: 14233

SELECT a . * , b. * , c . *
  FROM tbl_1 a
  LEFT JOIN tbl_2 b ON a.num = b.num
  LEFT JOIN tbl_3 c ON a.num = c.num
  ORDER BY a.num, b.num

Upvotes: 2

Related Questions