I have 2 tables, City(city_id, city_name) and count_staff_in_city(city_id, count_staff). the first table keeps the name of the city and its id, and the second table keeps the number of staffs working in each city. I want to join this 2 table in this way: if City.city_id exists in count_staff_in_city.city_id then show its value in select. if City.city_id does not exists in count_staff_in_city, then 0 should appear in output for this city.

how can I do this??


Use an outer join and replce NULLs using COALESCE:

SELECT C.city_name, COALESCE(CS.count_staff,0) AS count_staff
LEFT OUTER JOIN count_staff_in_city AS CS ON C.city_id = CS.city_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.