Multiple "and" statement in one-to-many table

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

General SQL Server Forums

New to SQL Server Programming

Multiple "and" statement in one-to-many table

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-22 : 11:04:23

I feel embarrassed having to ask this.

I have a table of roleID and adminID. An administrator can have multiple roles, and a role can have multiple administrators.

How can I select, in one statement, all the administrators which have a variable set of roleIDs?

So, one query might be all adminID which for which roleID = 7 and roleID = 1. Another might be all adminID for which roleID = 2 and roleID = 4 and roleID = 7

Does this make sense? I'm sure the answer is straightforward, but I had no sleep last night.

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:10:24

SELECT adminID
FROM table
GROUP BY adminID
HAVING COUNT(DISTINCT CASE WHEN roleID= 7 OR roleID = 1 THEN roleID ELSE NULL END)=2

SELECT adminID
FROM table
GROUP BY adminID
HAVING COUNT(DISTINCT CASE WHEN roleID= 7 OR roleID = 2  OR roleID = 4 THEN roleID ELSE NULL END)=3

...

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-22 : 11:15:22

That's great - much appreciated, thanks.

Is there any way to squeeze this into an SP? It's not clear to me how one could submit the correct argument (the roleIDs required) considering it's of variable length, unless maybe a varchar split by commas ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:28:45

[code]CREATE PROC RetrieveAdmin
@roleIDs varchar(8000)
AS
SELECT adminID
FROM table
GROUP BY adminID
HAVING COUNT(DISTINCT CASE WHEN roleID IN (Select Val FROM dbo.ParseValues(@roleIDs) THEN roleID ELSE NULL END)=(SELECT COUNT(*) FROM dbo.ParseValues(@roleIDs))
GO

[/code]

Parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-22 : 11:59:48

Interesting.

I'm not using ParseValues, as I have a user-defined function in my DB called fnSplit which looks to do the same job - take a varchar of delimited entities and return them as a table.

However, it looks as though the query is falling over on the subquery as part of a HAVING clause. As in using a subquery in a HAVING clause is not allowed.

I'm using SQL server 2000 - not sure if this makes any difference.

Anyone come across this as an issue? Or found a work-round?

Cheers,
Matt

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-22 : 12:06:27

quote:

Originally posted by visakh16

1.

SELECT adminID
FROM table
GROUP BY adminID
HAVING COUNT(DISTINCT CASE WHEN roleID= 7 OR roleID = 1 THEN roleID ELSE NULL END)=2

SELECT adminID
FROM table
GROUP BY adminID
HAVING COUNT(DISTINCT CASE WHEN roleID= 7 OR roleID = 2  OR roleID = 4 THEN roleID ELSE NULL END)=3

...

Use WHERE, not CASE. Much more efficient, shorter. easier.

SELECT adminID
FROM table
WHERE roleID in(2,7)
GROUP BY adminID
HAVING COUNT(DISTINCT roleID)=2

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 12:45:12

quote:
Originally posted by mattt

Interesting.

I'm not using ParseValues, as I have a user-defined function in my DB called fnSplit which looks to do the same job - take a varchar of delimited entities and return them as a table.

However, it looks as though the query is falling over on the subquery as part of a HAVING clause. As in using a subquery in a HAVING clause is not allowed.

I'm using SQL server 2000 - not sure if this makes any difference.

Anyone come across this as an issue? Or found a work-round?

Cheers,
Matt

Which subquery you're refering to?
Anyways try jsmiths soln. thats much more easier.

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-23 : 04:22:31

It was the first one, inside the IN statement.

But the other version works fine, thanks for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources