How to display staffs who never approved an order in MySQL

Question

i have these tables:

i want to display all names and department of staffs who did not approve an order. The code below shows me empty set.

SELECT s.SName, s.Department from tblstaff s 
INNER JOIN tblorder o
on s.staff_id=o.staff_id
WHERE o.order_id is Null;

http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — crafter, Apr 09 '15 at 15:36

score 0 · Answer 1 · answered Apr 09 '15 at 17:10

0

inner join will only give you results when items from table a and table b match. What you want is left join.

SELECT s.SName, s.Department 
from tblstaff s 
LEFT JOIN tblorder o
on s.staff_id=o.staff_id
WHERE o.order_id is Null;

This will give you all results from tblstaff, even if they have no match in tblorder.

answered Apr 09 '15 at 17:10

Mackan

Thanks for the perfect explanation and help, it works. – Stephen Okwechime Apr 09 '15 at 17:46
@StephenOkwechime You're welcome. Since you found my answer helpful, you could mark it as "answer" by pressing the check mark on the left. – Mackan Apr 09 '15 at 17:51

1 Answers1