Inner Join Twice In The Same Table

im having an issue with my inner join,these are my tables with FK and PK TABLE CITY city_id (PK) city_name state TABLE DEPOT dep_id (PK) capacity city_id (FK) references CiTy TAB

Solution 1:

List the table twice in the from clause with different aliases.

This will give you different cities, one for the manufacturer and one for the depot.

SELECT dc.city_name AS depot_city
,      mc.city_name AS manufacturer_city
FROM   DEPOT AS d
JOIN   CITY AS dc
    ON dc.city_id = d.city_id
JOIN   MANUFACTURER AS m
    ON m.some_column = d.some_column -- or however these tables relate
JOIN   CITY AS mc
    ON mc.city_id = m.city_id

Solution 2:

Just do two left joins on cityid :

select depot.city_id, Manufacturer.city_id from

depot  left join city on city.city_id = depot.city_id
       left join Manufacturer on Manufacturer.city_id = city.city_id