mysql - the inner join to the same table twice(ALIASES) -
this question has answer here:
- inner join twice in same table 2 answers
i have issue inner join,these tables fk
, 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 want make result this:
depot_city_name(references city_id)
, manufacturer_city_name(references city_id)
so depot_city_name
show name of city
depo
(references city.city_id
)
and manufacturer_city_name
show name of city
manufacturer
(references city.city_id
)
thanks
you need left outer join:
select c.city_id, d.depo_id, m.manu_id,c.city_name city c left outer join depo d on c.city_id=d.city_id left outer join manufacturer m on c.city_id=m.city_id
edit: if want separate city name depo , manufacturer use:
select c.city_id, d.depo_id, m.manu_id, case when d.depo_id null null else c.city_name end depot_city_name, case when m.manu_id null null else c.city_name end manufacturer_city_name city c left outer join depo d on c.city_id=d.city_id left outer join manufacturer m on c.city_id=m.city_id
Comments
Post a Comment