All geek questions in one place

MySQL: 4 has-many-through table Join?

Let's say I have the following 4 tables (for example): Owners, Trucks, Crates, Apples.

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

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

Say I want all the apples to “belong” to the owner with id = 34. Therefore, I want to get all the apples that are in the boxes that are in the trucks owned by the owner 34.

There is a “hierarchy” if you make 4 tables, each of which has a link only to its “parent”. How can I quickly filter fields when conditions are met in the other three tables?

Hope this made some sense.

Thanks.

+4
source share
3 answers
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
+4
source

You just start with the “top” (owners) and continue to join until you get what 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 such queries are often necessary, and you work with very large data sets, sometimes it makes sense to also denormalize the data. For example, adding owner_id to the apple table. This makes inserting / updating data more difficult, but can simplify query execution.

+1
source 
  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 by refusing to join the owners and simply choosing where t.owner_id = 34 if you no longer need information about the owner.

+1
source

Source: https://habr.com/ru/post/1306969/

More articles:


All Articles