For the sake of explaining my problem lets say I am building cars. In my DB a car is made out of parts (engine, chassis, wheel). Each part has a physical location (part_location from the table locations) which is linked via (part_location = location_id).
In my case, there will be only 1 of each part, each part is unique
In my query I am trying to assembly my cars in a query, including the city and country of each part (not the id)
The beginning of my query:
SELECT car_name, car_type, engine_name, engine_type, chassis_name, chassis_type, wheel_name, wheel_type
LEFT JOIN engines ON car_id = engine_car_id
LEFT JOIN chassis ON car_id = chassis_car_id
LEFT JOIN wheels ON car_id = wheel_car_id
Table engines (same for chassis, wheels)
I think the idea behind your query is correct. You should write it with explicit aliases, so I would expect something like this:
SELECT c.car_name, c.car_type, e.engine_name, e.engine_type, ch.chassis_name, ch.chassis_type, w.wheel_name, w.wheel_type, el.city as engine_city FROM cars c LEFT JOIN engines e ON e.car_id = c.engine_id LEFT JOIN chassis ch ON ch.car_id = c.chassis_id LEFT JOIN wheels w ON w.wheel_car_id = c.car_id LEFT JOIN locations el ON el.location_id = e.location_id
However, without the table layouts or sample data, this is just a guess.