mySQL错误的逻辑语句，不正确的下拉

Question

Attached is a picture that pretty much describes my entire problem:

Direct link: http://i.imgur.com/IAqqaWZ.png

I have a drop down table. Normally, it shows four entries, all the ones I want via this statement:

$sql="SELECT users.first_name, users.last_name, users.user_id, payments.fk FROM users, payments WHERE landlord = '0'";

So everything works up until I change up the sql line a bit to this:

$sql="SELECT users.first_name, users.last_name, users.user_id, payments.fk FROM users, payments WHERE landlord = '0' AND payments.fk != 'users.user_id'";

Basically, what I'm trying to select, is everyone that has the landlord flag set to 0 (which works fine in the previous code above), AND out of those people with the landlord flag set to 0, I want to only pick those people who aren't in the payments table. So basically, what should happen, is the drop down should only show three people.. Instead, just by adding that AND statement to my code, it now shows 4 people (4 times each for some reason), including the one person who is in the payments table.

Does anyone have any idea how I can fix this? Sorry for the crappy explanation.

Answer 1

Try using a LEFT JOIN, and check if the payment field IS NULL

SELECT users.first_name, users.last_name, users.user_id, payments.fk 
FROM users
LEFT JOIN payments 
ON payments.fk = users.user_id
WHERE landlord = 0 AND payments.fk IS NULL

SQLFiddle example - http://sqlfiddle.com/#!2/46a88c/2