CODEWITHSUNDEEP
mysqlsql
harish
harish

Reputation: 11

join 3 tables including null values

i have 3 tables table1 has the column id1 with three values in it '1','2','3' and table2 has column id2 with three values '3','4','5' and table3 has column id3 with three values '5','6','7' how do i join all the three tables, and i also want the null value to be displayed.pls go through the example below.

table1        |       table2        |      table3
----------------------------------------------------   
 id1          |       id2           |      id3
----------------------------------------------------
  1           |        3            |       5
  2           |        4            |       6
  3           |        5            |       7

the output which i expect is

id1  |  id2   |  id3
----------------------
 1   |  null  | null
 2   |  null  | null
 3   |   3    | null
null |   4    | null
null |   5    | 5
null |  null  | 6
null |  null  | 7

some1 pls do help me am confused

Upvotes: 0

Views: 1594

Answers (3)

Jyothi
Jyothi

Reputation: 121

This query gives you the output you are looking for:

select t1.id1, t2.id2, t3.id3
from table1 t1
left join table2 t2
on t1.id1 = t2.id2
left join table3 t3
on t2.id2 = t3.id3
UNION 
select t1.id1, t2.id2, t3.id3
from table2 t2
left join table3 t3
on t2.id2 = t3.id3
left join table1 t1
on t2.id2 = t1.id1
UNION 
select t1.id1, t2.id2, t3.id3
from table3 t3
left join table2 t2
on t3.id3 = t2.id2
left join table1 t1
on t3.id3 = t1.id1;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
|    3 |    3 | NULL |
|    1 | NULL | NULL |
|    2 | NULL | NULL |
| NULL |    5 |    5 |
| NULL |    4 | NULL |
| NULL | NULL |    6 |
| NULL | NULL |    7 |
------+------+------+
7 rows in set (0.00 sec)

Upvotes: 1

evilpenguin
evilpenguin

Reputation: 5478

You need a full outer join, unfortunately, MySQL does not have full outer joins. There is a request, but it's still open.

You have to emulate them by doing unions between several left joins, circularly:

SELECT id1, id2, id3
FROM
    table1
    LEFT JOIN table2 on table1.id1 = table2.id2
    LEFT JOIN table3 on table2.id2 = table3.id3
UNION
SELECT id1, id2, id3
FROM
    table2
    LEFT JOIN table3 on table2.id2 = table3.id3
    LEFT JOIN table1 on table3.id3 = table1.id1
UNION
SELECT id1, id2, id3
FROM
    table3
    LEFT JOIN table1 on table3.id3 = table1.id1
    LEFT JOIN table2 on table1.id1 = table2.id2

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use union query

Declare @tbl1 as table
(
id1 int
)
Declare @tbl2 as table
(
id2 int
)

Declare @tbl3 as table
(
id3 int
)

insert into @tbl1 values(1)
insert into @tbl1 values(2)
insert into @tbl1 values(3)

insert into @tbl2 values(3)
insert into @tbl2 values(4)
insert into @tbl2 values(5)

insert into @tbl3 values(5)
insert into @tbl3 values(6)
insert into @tbl3 values(7)

SELECT
*
FROM
(
    Select 
        T1.Id1,
        T2.Id2,
        T3.Id3
    FROM @tbl3 T3
    LEFT JOIN @tbl1 T1 ON T1.id1=T3.id3
    LEFT JOIN @tbl2 T2 ON T3.id3=T2.id2
    UNION
    Select 
        T1.Id1,
        T2.Id2,
        T3.Id3
    FROM @tbl1 T1
    LEFT JOIN @tbl2 T2 ON T1.id1=T2.id2
    LEFT JOIN @tbl3 T3 ON T1.id1=T3.id3
    Union
    Select 
        T1.Id1,
        T2.Id2,
        T3.Id3
    FROM @tbl2 T2
    LEFT JOIN @tbl1 T1 ON T1.id1=T2.id2
    LEFT JOIN @tbl3 T3 ON T2.id2=T3.id3
)X
Order by ISNULL(X.id1,9)  ,ISNULL(X.id2,9),X.id3

Upvotes: 0

Related Questions