So i have two tables with information below one which is a holiday table which marks the location to and from and etc. and another table that has the information on the locations

I'd like to have an sql query which will return the names of the location to and from but because location name is under one column im not sure how to do it. any help is appreciated

CodePudding user response：

You can join with the table, twice, using an alias:

select location_1.location_name, location_2.location_name, other, columns
from holidays
join locations location_1 on holidays.location_from = location_1.location_id
join locations location_2 on holidays.location_to   = location_2.location_id

CodePudding user response：

Just need to join to the location table twice. Once for the location_from and again for the location_to:

SELECT
    h.holiday_id,
    h.location_from,
    locfrom.location_name,
    h.location_to,
    locto.locatin_name,
    h.start_time,
    h.end_time
FROM    Holidays h
INNER JOIN Locations AS locfrom
    ON locfrom.Location_id = h.location_from
INNER JOIN Locations locto
    ON locto.Location_id = h.location_to;

CodePudding user response：

select a.location_name,b.location_name from table1 l
left join table2 a on a.Location_id = l.location_from
left join table2 b on a.Location_id = l.location_to