Need some help with sql...

Pests

I have two tables. One called user and one called invite.

user
id
username
squad_id

invite

id
user_id
squad_id

I want to pull out all of the rows in user that have a squad_id of 0 and the rows id is not in the user_id of the invite feild.

Example:

user
1|user1|0
2|user2|0
3|user3|1
4|user4|2

invite
1|1|14
2|3|23

I want the query to return row 2 (because squad_id is 0, and the id is not in the user_id column of the invite table). The squad_id of the invite feild doesnt matter.

Any help would be great.

eoghain

SELECT *
  FROM user
 WHERE squad_id = 0
   AND id NOT IN (SELECT user_id FROM invite);

Ofcourse this will only work on a database that allows subqueries.

tomhath

Assuming mysql (because it's easier with other dbms' using a subselect) you should use a LEFT JOIN. It looks wrong but actually works. 😃

select user.*
from user
left join invite
on user.id = invite.user_id
where user.squad_id = 0
and invite.id is null

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

Sxooter

Actually, using a left join or an except clause is almost always a better idea than an in clause. Performance wise a left join should be the fastest, and except next, then the in clause last, at least when run on Postgresql.

So, even though you can do the in() on postgresql, it's relative slowness means you're better off avoiding it.