Join Help

[schilly]

I have a users and orders table. Both share the username field. I need to find all users who have no orders associated with their account.

 

I'm pretty sure it needs a join but I don't have much practise with them so some help is needed.

 

I'm guessing something like:

 

select * from users INNER JOIN orders ON users.username = orders.username where (SELECT sum(id) FROM orders WHERE users.username = orders.username) = 0

 

I'm not getting any errors but also getting no results.

 

Thx.

[fenway]

You need a LEFT JOIN... IS NULL query.

[schilly]

awesome. thanks so much. worked perfect.

 

for the record, here's what I changed to:

select * from users LEFT JOIN orders ON users.username = orders.username where (SELECT sum(id) FROM orders WHERE users.username = orders.username) IS NULL