SELECT all NOT in 2nd Table

quaffapint

Ok - here's the DB setup...

usertbl

uid | uname

1 bob
2 jane

usergrouptbl

gid | uid

The usertbl holds a list of current user's and their ids. The usergrouptbl holds the group memberships of those users (gid = GroupID, uid= UserID).

I want a query that will find all users NOT in a particular group.

It sounds like it should be easy, but I'm just not seeing it...😕

Thanks in advance...
Quaff

tomhath

THis question is asked frequently. As an exercise you might want to hit the "Search" button in the upper right corner of this page and see if you can find the answer (it's usually faster than waiting for a reply to a post). Here's one you might find:

http://www.phpbuilder.com/board/showthread.php?threadid=10246163

quaffapint

I can honestly say I searched for a half an hour. 🙁

I did not find anything matching exactly what I needed. Maybe I didn't know the correct terms to search for.

I found the post you pointed to earlier, but just couldn't see how to make that apply to my problem. The problem always seems to be the WHERE gid=$groupid. When there is noone in the group I should get a list of every user, but the WHERE kills that since there is no gid with value $groupid.

-Quaff

______

Originally posted by quaffapint
Ok - here's the DB setup...

usertbl

uid | uname

1 bob
2 jane

usergrouptbl

gid | uid

based on this table layout:

SELECT uid, uname FROM usertbl
LEFT OUTER JOIN usergrouptbl
ON usertbl.uid = usergrouptbl.uid
WHERE usergrouptbl.gid IS null

😃

quaffapint

Thanks for the reply - but, I stupidly am not getting my idea across.

The problem is not where 'usergrouptbl.gid' is null, but rather when it is some value.

So, if I'm looking at GroupA - Id of 1, I want to look up in the user's table all the user ids then compare that list to those user id's that are paired up with group id of 1 in the groups table, and the result set should be the user id's from the user table that found no match.

I hope I made that clear...🙂

-Quaff

Bijan

SELECT usergrouptbl.uid, usertbl.name
FROM usergrouptbl, usertbl
WHERE usergrouptbl.gid != [color=red]2[/color] AND usertbl.id = usergrouptbl.uid

I assumed that you want all the ids not in a group with the gid of 2, it means you should change that 2 to any gid that you want in future!

______

Originally posted by quaffapint
Thanks for the reply - but, I stupidly am not getting my idea across.

No, i stupidly translated wrong... 😃

SELECT DISTINCT usertbl.uid
FROM usergrouptbl
RIGHT OUTER JOIN usertbl
ON usertbl.uid = usergrouptbl.uid
WHERE usergrouptbl.gid is null
OR usergrouptbl.gid != [the particular! gourpid]

hope this is what you wanted... :rolleyes:

______

Originally posted by ()((()
hope this is what you wanted... :rolleyes:

Ah, there might be an error in it, but it's too late and i have no data to try on...

But i think it's the right direction... perhaps... :p

tomhath

The LEFT JOIN will return a row for every row in the "left" table (in this case "usertbl"). Some join to a row in usergrouptbl and will have a value for gid, some will have NULL as the gid value (when there is no usergrouptbl row to join with).

The WHERE will filter out the ones that have a gid, leaving only "users NOT in a particular group"

SELECT uid, uname FROM usertbl 
LEFT OUTER JOIN usergrouptbl 
ON usertbl.uid = usergrouptbl.uid 
AND usergrouptbl.gid = 1
WHERE usergrouptbl.gid IS null

quaffapint

Thanks for all the replies. I'll see where I get with them this weekend.

Appreciate it...🙂

-Quaff

Bijan

Well, I tested my query on a real DB and it worked!!

______

Originally posted by Bijan
Well, I tested my query on a real DB and it worked!!

Just a short note: I'm again too sleepy to do the query in mind :o , but your idea seems to be quite right as through the '=' nulls are not included (i think), which was presumably the flaw in my second query 😃

But what i don't like is the AND in the query... these indicate (on implicit joins) a cartesian product, which means every row in the first table is linked with every row on the second. That leads, if you are unlucky and your DB is rather big, to exorbitant temporary tables the WHERE-clause is then applied to, e.g. 1.000.000 rows if each table has 1000 entries... (some DBMS do optimization, though)

But it's definately possible to break this down to a join (it's always).
Otherwise, GOOD WORK!
😃 😃 😃

Bijan

Ok, I admit that I'm an amateur in these sort of things (joining and stuff!), but then thank you for that GOOD WORK! 😃 😃

PS: It seems we have kinda mutual feelings, most of the times I feel sleepy too, like now!