Select query help - 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

General SQL Server Forums

New to SQL Server Administration

Select query help

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-06-28 : 11:15:20

I have a table called ReviewList. The table has the following columns with some sample data:

ReviewListAppId | ReviewListId | ApplicantId | StatusId | CreatedOn |
1 | 2 | 6 | 1 | 1/1/2010
2 | 2 | 6 | 3 | 1/1/2010
3 | 2 | 6 | 3 | 1/2/2010
4 | 3 | 7 | 1 | 1/2/2010
5 | 4 | 8 | 3 | 1/2/2010
6 | 4 | 8 | 3 | 1/3/2010

I want to find any ApplicantId within the table that has only 3 as a StatudId assigned to it. Therefore the query should return ApplicantId 8 but NOT 6. Any help is appreciated.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-28 : 11:35:53

[code]
SELECT ApplicantId
FROM ReviewList
GROUP BY ApplicantId
HAVING COUNT(StatusId) = COUNT(CASE WHEN StatusId = 3 THEN 1 END)
[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 11:50:27

Very nice



CREATE TABLE #myTable99(
	ReviewListAppId int, ReviewListId int, ApplicantId int,  StatusId int,  CreatedOn datetime)
GO

INSERT INTO #myTable99(
	ReviewListAppId, ReviewListId, ApplicantId,  StatusId,  CreatedOn)
SELECT 1 , 2 , 6 , 1 , '1/1/2010' UNION ALL
SELECT 2 , 2 , 6 , 3 , '1/1/2010' UNION ALL
SELECT 3 , 2 , 6 , 3 , '1/2/2010' UNION ALL
SELECT 4 , 3 , 7 , 1 , '1/2/2010' UNION ALL
SELECT 5 , 4 , 8 , 3 , '1/2/2010' UNION ALL
SELECT 6 , 4 , 8 , 3 , '1/3/2010'
GO


SELECT ApplicantId, COUNT(StatusId), COUNT(CASE WHEN StatusId = 3 THEN 1 END)
FROM #myTable99
GROUP BY ApplicantId


SELECT ApplicantId
FROM #myTable99
GROUP BY ApplicantId
HAVING COUNT(StatusId) = COUNT(CASE WHEN StatusId = 3 THEN 1 END)

DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

iradev
Starting Member

45 Posts

Posted - 2010-06-29 : 06:06:44

Thank you both, solution works perfectly!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 08:40:50

To avoid unneccessary NULL warnings, use

SUM(CASE WHEN StatusId = 3 THEN 1 ELSE 0 END)

instead of

COUNT(CASE WHEN StatusId = 3 THEN 1 END)

Madhivanan

Failing to plan is Planning to fail

iradev
Starting Member

45 Posts

Posted - 2010-07-06 : 11:26:39

If there was more than one StatusId that I want to avoid, lets say 2, 4 and 5? Anyone?

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-07-12 : 09:05:25

Use in Clause like below

SUM(CASE WHEN StatusId in (2,3,4,5) THEN 1 ELSE 0 END)

iradev
Starting Member

45 Posts

Posted - 2010-07-12 : 09:46:45