Posted by Erland Sommarskog on 10/02/91 11:55

Henrik Goldman (henrik_goldman@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Selecting on multiple tables
• Prev in forum: Re: format files for use with bulk insert
• Thread view: Re: Selecting on multiple tables

[Reply to this message]