selecting groups user is not a member of 1

[y2k1981]

OK, i've got the following 3 tables:
users
userID
name
...

groups
groupID
group_name
...

usersingroups
userID
groupID

What I want to do is SELECT * FROM groups where userid 1 does not have a corresponding record in the usersingroups table. Sorry, I know that's not very good english, but hopefully you understand what I'm trying to say. I can't even say it properly in english, so I don't know how to put it into SQL!!

Also, I used something similar to the following to get out the groups the user is a member of. It works, but I just wondered if it wasn considered bad programming practice, using left join on left join:

SELECT * FROM groups LEFT JOIN usersingroups using (groupID) LEFT JOIN users using (userID) where users.userID = 1;

Can somebody tell me if that's OK, or if I should have done it differently?

 

[r937]

select groups.* 
  from groups 
left outer
  join usersingroups 
    on groups.groupID
     = usersingroups.groupID
   and usersingroups.userID = 1
 where usersingroups.userID is null

rudy

http://r937.com/

SQL Consulting

 

[y2k1981]

Cheers rudy, that works perfect. But would you mind explaining a few things to me?

what's the different between a left join and left outer join. would you mind explaining to me what exactly it's doing, as I can't seem to get my head around it.

thanks again

 

[r937]

LEFT JOIN is identical to LEFT OUTER JOIN (the keyword OUTER is optional)

what it's doing is trying to join to a matching row in usersingroups based on groupID with userID = 1

when a left outer join doesn't find a matching row in the right table, all columns from the right table are set to null for that left table row

this is why you test for IS NULL in the WHERE clause

the result is all rows from the left table which don't have matching rows in the right table, based on the join conditions


rudy

http://r937.com/

SQL Consulting

 

[y2k1981]

Cool, thanks for the excellent explanation. Definitely earned that star !!