CODEWITHSUNDEEP
mysqlsqlinner-join
Luke
Luke

Reputation: 13

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

TABLE MANUFACTURER
manu_id (PK)
manu_name
city_id (FK) references city

so i just want to make the result look like this:

DEPOT_CITY_name(referencese from city_id), MANUFACTURER_CITY_name(references from city_id)

thanks

Upvotes: 0

Views: 422

Answers (2)

Erik Blessman
Erik Blessman

Reputation: 693

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

Upvotes: 1

Nightmaresux
Nightmaresux

Reputation: 538

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

Upvotes: 0

Related Questions