mysql multi layered self referencing nested join

Question

I have 2 tables. tb_device and tb_site_object.

tb_device is quite simple:

| id | station_id |
|----|------------|
| 1  | 6          |
| 2  | 6          |
| 3  | 9          |

tb_site_object looks like this:

| id | type    | upper_site_id |
|----|---------|---------------|
| 1  | top     | null          |
| 2  | type2   | 1             |
| 3  | type3   | 2             |
| 4  | station | 3             |
| 5  | top     | null          |
| 6  | station | 5             |

The relationship between tables is like this: tb_site_object has many devices when type column in tb_site_object is equal to station.

As can be seen tb_site_object table self references itself. Rows with station type points to another row with type type3 and rows with type3 references rows with type2 and naturally they also reference top and close the climbing loop via upper_site_id. Since top rows start the chain they have always null value for upper_site_id.

There is a but: type station can point to any type above breaking the station.

Assuming I explained the tables correctly (hopefully), this is my question: I need to make an sql query for fetching devices given with only id of tb_site_object.

so if id is 1 I have to crawl down to 4 and make a join with tb_device. if given id is 3 then it's straight to 4.

this is how I am fetching stations:

select
  lvl1.type lvl1_type,
  lvl1.upper_site_id lvl1_upper,
  lvl2.type lvl2_type,
  lvl2.upper_site_id lvl2_upper,
  lvl3.type lvl3_type,
  lvl3.upper_site_id lvl3_upper,
  lvl4.type lvl4_type,
  lvl4.upper_site_id lvl4_upper
from
  tb_site_object as lvl1
  left JOIN tb_site_object lvl2 ON lvl1.id = lvl2.upper_site_id
  left JOIN tb_site_object lvl3 ON lvl2.id = lvl3.upper_site_id
  left JOIN tb_site_object lvl4 ON lvl3.id = lvl4.upper_site_id
where
  lvl1.type = 'business';

EDIT 1 station_id in tb_devices is foreign key for tb_site_object's id.

EDIT 2 Type column always starts with top and ends with station. top -> type2 -> type3 -> station. Only station can go up and end the chain early

Your description contains no description of how the two tables are related. Where is the reference from one table to the other? You mention "make a join with tb_device". Explain how the tables are related. — Jon Armstrong, Apr 14 '22 at 08:08
Which row relates to device? Top,station or something else? which column relates to device? Are the top...station always discrete and always a station for a top? If there is a top without a station what do you want? Can top...station overlap eg top,top,station,top ? — P.Salmon, Apr 14 '22 at 08:08
It may be that recursion is appropriate what version of mysql are you on? — P.Salmon, Apr 14 '22 at 08:11
`station_id` column in tb_devices points to `id` in tb_site_object @JonArmstrong — Javohir Mirzo Fazliddinov, Apr 14 '22 at 08:13
You must have a look at https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — CarlosSR, Apr 14 '22 at 08:14
It always starts with top and ends with station. top -> type2 -> type3 -> station. Only station can go up and end the chain early @P.Salmon — Javohir Mirzo Fazliddinov, Apr 14 '22 at 08:15

mysql multi layered self referencing nested join

0 Answers0