Selecting on multiple tables

[Henrik Goldman]

Hi there,

I'm new to sql and thus I'm having problems with a specific query which I
hope you guys can help me with.

Basicly I have a few tables which I'm trying to do a query on:

Table groups contains information about specific groups e.g. "Windows" or
"Unix".
Table users contains information about specific users e.g. "a", "b" or "c".
Table users_groups contain information about group relationship (a user can
be in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).
In this case user c is ungrouped.

Now I'd like to find the users which does belong to group Windows and those
who do not:

select distinct username from users_groups where groupname = "Windows" order
by username asc;

This works pretty well for finding users in the specific group. In this case
the result is a.

However I'd like to get the opposite result (b and c) but I'm stuck.

The problem is that I'd like a list of all users excluding those which are
in "Windows"

Here is a partial query:

select distinct users.username from users left join users_groups on
users.username = users_groups.username where users_groups.username is null
order by users.username asc;

This only gives me those users who are not grouped at all. This mean that
user b is not in those results.

Please advise.

Thanks in advance.

-- Henrik

[Erland Sommarskog]

Henrik Goldman (he************@mail.tele.dk) writes:

Table groups contains information about specific groups e.g. "Windows"
or "Unix". Table users contains information about specific users e.g.
"a", "b" or "c". Table users_groups contain information about group
relationship (a user can be in multiple groups) e.g. (a, Windows), (b,
Unix), (a, Unix).
In this case user c is ungrouped.

Now I'd like to find the users which does belong to group Windows and
those who do not:
...
However I'd like to get the opposite result (b and c) but I'm stuck.

SELECT u.username
FROM users u
WHERE NOT EXISTS (SELECT *
FROM users_groups ug
WHERE u.userid = ug.userid
AND ug.group = 'Windows')

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Henrik Goldman]

SELECT u.username

FROM users u
WHERE NOT EXISTS (SELECT *
FROM users_groups ug
WHERE u.userid = ug.userid
AND ug.group = 'Windows')

Thanks thats perfect. I didn't even know you could do that syntax. I guess I
learned something new today.

-- Henrik