EXPERT RESPONSE
There are two ways to do it. The first is to start with
a list of all the userids that are in the Adg group
in the usergroup table, and then use this list to filter the
users from the user table, so that you are left with users that aren't.
You can do this in a single query with a NOT EXISTS subquery:
select userid
, realname
from users_users
where userid NOT IN
( select userid
from users_usergroups
where groupname in ('Adg')
)
Some databases (actually only one that I
know of, but it's extremely popular -- MySQL prior to version 4.1)
do not support subqueries, so there's an alternate method that
works just as nicely, using a LEFT OUTER JOIN:
select U.userid
, U.realname
from users_users U
left outer
join users_usergroups UG
on U.userId = UG.userId
and UG.groupname in ('Adg')
where UG.userId IS NULL
Essentially, you start with the rows in the left table,
all of which will be returned in a LEFT OUTER join,
whether there are matching rows from the right table or not.
Then in the join's ON clause, you attempt to match to the specific
usergroup rows for 'Adg'. In any LEFT OUTER join, if no match is found,
the columns from the right table on that particular row will be null.
These are the rows you want, and the WHERE clause effectively filters
all the other rows out.
Some people have trouble seeing the ON condition requiring
a match on a key, and then testing the same key for NULL in the WHERE
clause, but it does make sense, and it simply takes advantage
of the way outer joins work. Note that if the groupname in ('Adg')
condition were placed in the WHERE clause and not the ON clause,
the results would not be correct.
For More Information
|
