MySQL: 4 Table "has-many-through" Join?

Question

Let's say I have the following 4 tables (for examples' sake): Owners, Trucks, Boxes, Apples.

An owner can have many trucks, a truck can have many boxes and a box can have many apples.

Owners have an id. Trucks have an id and owner_id. Boxes have an id and truck_id. Apples have an id and box_id.

Let's say I want to get all the apples "owned" by an owner with id = 34. So I want to get all the apples that are in boxes that are in trucks that owner 34 owns.

There is a "hierarchy" if you will of 4 tables that each only has reference to its direct "parent". How can I quickly filter boxes while satisfying conditions across the other 3 tables?

I hope that made sense somewhat.

Thanks.

score 4 · Accepted Answer · answered Apr 14 '10 at 19:59

4

select a.* 
from Trucks t
inner join Boxes b on t.id = b.truck_id
inner join Apples a on b.id = a.box_id
where t.owner_id = 34

answered Apr 14 '10 at 19:59

D'Arcy Rittich

167,292
40
290
283

Brilliant, thanks! Everyone pretty much gave me the same answer but this one is more appropriate vs Eric's response because I don't need access to the Owner's info in this case. – nebs Apr 14 '10 at 20:18

score 1 · Answer 2 · answered Apr 14 '10 at 20:00

You just start at the "top" (owners) and keep joining until you get where you want:

SELECT a.*
FROM Owners o
INNER JOIN Trucks t ON t.owner_id = o.id
INNER JOIN Boxes b on b.truck_id = t.id
INNER JOIN Apples a on a.box_id = b.id
WHERE o.id = ?

If queries like that are needed often and you are working with very large data sets, sometimes it makes sense to denormalize the data a bit as well. For example by adding the owner_id to the apples table. It makes inserting/updating the data a bit more difficult, but can make queries easier.

Thanks for the tip about starting from the "top". I'm still new to sql joins so simple tips like that really help out. — nebs, Apr 14 '10 at 20:26

score 1 · Answer 3 · answered Apr 14 '10 at 20:02

    SELECT a.*
      FROM Apples a
INNER JOIN Boxes b ON b.id = a.box_id
INNER JOIN Trucks t ON t.id = b.truck_id
INNER JOIN Owners o ON o.id = t.owner_id
     WHERE o.id = 34

You can simplify this somewhat by leaving out the join to owners and just selecting where t.owner_id = 34 if you don't need any information about the owner later.

MySQL: 4 Table "has-many-through" Join?

3 Answers3

Linked