0

please help me with this I have two tables, the first one is province with province_id(primary key), province_name, propose that province_id could be null

the second is staff with staff_id(primary key), staff_name, province_id

the question is display staff_id, staff_name, province_name, and any staff who does not belong to any province, display "none", use only one query

I did this

SELECT staff_id, staff_name, province.province_name
FROM staff
INNER JOIN province
ON staff.province_id = province.province_id;

Please help me to insert none value for those staff who not belong to any province.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Kuc Ku
  • 51
  • 1
  • 6

1 Answers1

0

If you're looking for staff members who don't belong to any province, you should use an outer join, not an inner one. From there, simply apply ifnull to the result:

SELECT          staff_id, staff_name, IFNULL(province.province_name, 'none')
FROM            staff
LEFT OUTER JOIN province
ON              staff.province_id = province.province_id;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thanks so much, I really appreciate it, you save me :), cheers – Kuc Ku Dec 30 '13 at 18:45
  • @KucKu you're welcome. You can accept the answer in order to mark that it solves the issue in case someone else with a similar problem comes over this page. – Mureinik Dec 30 '13 at 19:58