Select - 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)

Select

Author

Topic

bbis25
Starting Member

2 Posts

Posted - 2005-07-15 : 02:56:11

Hi all,
Outlining a problem I am currently facing with a TSQL query.

Table structure is as follows

RoleID ConID Name Role
1 1 A Donor
2 1 A Client
3 2 B Volunteer
4 2 B Client
5 3 C Volunteer
6 4 D Volunteer
7 4 D Client
8 4 D Donor
9 5 E Volunteer
10 5 E Next of Kin
11 5 E Donor
12 5 E Doctor

Objective - Select people with more than one role,of which two roles have to be volunteer and client roles.

Therefore from the above table,only contacts 2 and 4 would be returned.I have tried every which way to do this,is this a cursor problem,if so how would this be implemented.
If i'm missing something blindingly obvious,I appologise in advance,but would appreciate some help.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-07-15 : 03:14:47

[code]
select name
from t
where role in ('Client','Volunteer')
group by name
having count(*) = 2
[/code]

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-15 : 03:15:38

Does this work for you

SELECT a.*
FROM YourTable a 
	INNER JOIN (SELECT ConID, COUNT(ConID) AS TheCount 
			FROM YourTable 
			WHERE Role IN ('Client','Volunteer')
			GROUP BY ConID) b 
	ON a.ConID = b.ConID
WHERE b.TheCount >= 2

Andy

Beauty is in the eyes of the beerholder

bbis25
Starting Member

2 Posts

Posted - 2005-07-15 : 03:33:59

AndyB,
Thanks for the solution,seems to work fine.
Best Regards
bbis25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources