mysql - the inner join to the same table twice(ALIASES) -


this question has answer here:

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

Popular posts from this blog

c++ - Delete matches in OpenCV (Keypoints and descriptors) -

java - Could not locate OpenAL library -

sorting - opencl Bitonic sort with 64 bits keys -