MySQL Multiple connections without concatenation into one line
I have five tables as shown below and the result at the end I would like to exit from a query where each row is a single ID from one of the tables c1
, c2
or c3
, together with their respective IDs a
and b
.
The query I am trying to execute is currently running and the result set I am currently getting is ending.
and
+----+
| 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 |
+----+------+-------+-------+-------+
request
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 |
+----+------+-------+-------+-------+
+3
source to share
1 answer
try this one I hope it is 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
)
+2
source to share