Jelmer Jelmer - 2 months ago 5
SQL Question

SQL LEFT JOIN multiple tables including link to 1 table for each join

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
FROM cars
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


How can I include the location of each part (e.g. car_city, car_country; an alias for each part from location_city, location_country)?

My tables are set up as follows:

Table cars
car_id
car_name
car_type

Table engines (same for chassis, wheels)
engine_id
engine_name
engine_type
engine_car_id
engine_location_id

Table locations
location_id
location_city
location_country

Answer

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.

Comments