JOIN, INTERSECT or Whatever - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN, INTERSECT or Whatever

Author

Topic

kruiseri
Starting Member

3 Posts

Posted - 2006-01-18 : 16:16:07

Hi,

I'm on SQL 2000, and I need to come up with a query to produce the following.

I have two tables, Persons and GroupMemberships. Each Person in Persons table has a unique ID, each person can have multiple rows in the GroupMemberships table, each row meaning he/she is a member in that group.

If I do this

SELECT * FROM Persons WHERE ID IN(SELECT DISTINCT PersonID FROM GroupMemberships WHERE Group IN('Group1','Group2','Group3))

I get a result set of persons that is a member of any of those three groups (OR Condition). No problem here.

But if I want to return persons that are members in ALL of those groups (AND condition) I'm totally lost. SQL 2000 does not have the INTERSECT clause that might solve this, and I believe the EXISTS can only do what IN already does.

Any ideas ??

Petri

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-18 : 17:04:18

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-01-18 : 18:48:09

quote:
Originally posted by kruiseri
SQL 2000 does not have the INTERSECT clause that might solve this, and I believe the EXISTS can only do what IN already does.

INTERSECT would NOT solve this.. You need a DIVISION operator.

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

kruiseri
Starting Member

3 Posts

Posted - 2006-01-19 : 14:19:29

quote:

Originally posted by blindman

SELECT	Persons.*
FROM	Persons
inner join --Groups
	(select	PersonID
	from	GroupMemberships
	where	Group IN('Group1','Group2','Group3)
	group by PersonID
	having count(distinct GROUP) = 3) Groups
	on Persons.ID = Groups.PersonID

I guess this could work, however I got this error
Server: Msg 409, Level 16, State 2, Line 1
The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument.

Forgot to mention (Didn't realize that it mattered) all the ID fields are type uniqueidentifier.

Why can't you count the number of uniqueidentifiers anyway...

kruiseri
Starting Member

3 Posts

Posted - 2006-01-19 : 14:34:44

One more thing.

Looking at the query and trying to understand it, wouldn't it return all the persons that are members in exactly those three groups ?

What I mean is that if a person is a member in all those three groups plus one more group that we failed to mention, he would not be returned with this query?

The business logic here requires that they are to be returned as well, so how should I amend the query to accomodate for that.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-19 : 16:19:46

[code]SELECT Persons.*
FROM Persons
inner join --Groups
(select PersonID
from GroupMemberships
where Group IN('Group1','Group2','Group3)
group by PersonID
having count(distinct GROUP) = 3) Groups
on Persons.ID = Groups.PersonID[/code]

OK, I know this is not EXACTLY the code you ran, because it has a syntax error (missing single-quote).
So...your error message "count-unique aggregate operation cannot take a uniqueidentifier data type as an argument" makes no sense with this code, because the count operation is being performed against the [group] column, which does not appear to be a GUID.
But...your concern about excluding persons who are members in these three groups plus one or more additional groups is misfounded. The IN clause ensures that only affiliations with those three groups are counted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources