SQL – Fetch associated data from multiple table

I have 4 tables. The table does not contain any foreign key reference

t1:

| id  | name           |
+-----+----------------+
| a1  | cheese         |
| a2  | butter         |
| a3  | milk           |

t2:

| id  | name        |   t1_id      |
+-----+-------------+--------------+
| b1  | item1       | a1           |
| b2  | item2       | a2           |
| b3  | item3       | a3           |

t3:

| id  | name        |   t2_id      |
+-----+-------------+--------------+
| c1  | item4       | b1           |
| c2  | item5       | b2           |
| c3  | item6       | b3           |

t4:

| id  | name        |   t3_id      |
+-----+-------------+--------------+
| d1  | item7       | c2           |
| d2  | item8       | c3           |

I need to get all associated data.

When I specify ‘a1’ I expect something as,

| name     | name        |   name       |   name       |
+----------+-------------+--------------+--------------+
| cheese   | item1       |item4         |              |

I used the following query,

SELECT a.name, b.name, c.name, d.name FROM t1 AS a 
INNER JOIN t2 AS b ON b.id = c.id 
INNER JOIN t3 AS c ON c.id = b.id 
INNER JOIN t4 AS d ON d.id = c.id;

This fetches no result as INNER JOIN t4 AS d ON d.id = c.id does not have matching value.

How to return the intermediate result? Is this query optimized (or) should I write separate queries to fetch this? How to achieve this?

Answer

Do you just want left joins?

SELECT a.name, b.name, c.name, d.name
FROM t1 AS a LEFT JOIN
     t2 AS b
     ON b.id = c.id LEFT JOIN
     t3 AS c
     ON c.id = b.id LEFT JOIN
     t4 AS d
     ON d.id = c.id;

Advertisement

Answer