CODEWITHSUNDEEP
mysqljoin
bdx
bdx

Reputation: 3516

MySQL Multiple joins without combining to same row

I have five tables as follows, and the result set at the end I'd like to get out of a query, where each row is one id from one of the c1, c2 or c3 tables, along with their corresponding a and b id's.

The query I'm currently trying and the result set I'm currently getting is at the end.

a

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

b

+----+------+
| id | a_id |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    2 |
+----+------+

c1

+-----+------+
| id  | b_id |
+-----+------+
| c11 |    1 |
| c12 |    2 |
+-----+------+

c2

+-----+------+
| id  | b_id |
+-----+------+
| c21 |    1 |
| c22 |    3 |
+-----+------+

c3

+-----+------+
| id  | b_id |
+-----+------+
| c31 |    2 |
| c32 |    3 |
+-----+------+

desired query result

+----+------+-------+-------+-------+
| id | b_id | c1_id | c2_id | c3_id |
+----+------+-------+-------+-------+
|  1 |    1 | c11   |       |       |
|  1 |    2 | c12   |       |       |
|  1 |    1 |       | c21   |       |
|  2 |    3 |       | c22   |       |
|  1 |    2 |       |       | c31   |
|  2 |    3 |       |       | c32   |
+----+------+-------+-------+-------+

query

SELECT a.id, b.id, c1.id, c2.id, c3.id
FROM a
INNER JOIN b ON b.a_id = a.id
LEFT JOIN c1 ON c1.b_id = b.id
LEFT JOIN c2 ON c2.b_id = b.id
LEFT JOIN c3 ON c3.b_id = b.id

actual result

+----+------+-------+-------+-------+
| id | b_id | c1_id | c2_id | c3_id |
+----+------+-------+-------+-------+
|  1 |    1 | c11   | c21   |       |
|  1 |    2 | c12   |       | c31   |
|  2 |    3 |       | c22   | c32   |
+----+------+-------+-------+-------+

Upvotes: 3

Views: 58

Answers (1)

Manisha Patel
Manisha Patel

Reputation: 354

try This one Hope it useful to you

 (
   SELECT a.id, b.id, c1.id as c1id, "" as c2id, "" as c3id
   FROM a
   INNER JOIN b ON b.a_id = a.id
   INNER JOIN c1 ON c1.b_id = b.id
 ) 
 union 
 (
   SELECT a.id, b.id,"" as c1id, c2.id as c2id, "" as c3id
   FROM a
   INNER JOIN b ON b.a_id = a.id
   INNER JOIN c2 ON c2.b_id = b.id
 ) 
 union 
 (
   SELECT a.id, b.id,"" as c1id,"" as c2id, c3.id as c3id
   FROM a
   INNER JOIN b ON b.a_id = a.id
   INNER JOIN c3 ON c3.b_id = b.id
 )

Upvotes: 2

Related Questions