subquery with not exists? - 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 Programming

subquery with not exists?

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-07-09 : 06:58:50

I have a table called Rounds with following columns: RoundId, ApplicantId, GroupId and Status. Below is some sample data:

RoundId..... 1|2|3|4|5|6|7|8|9|10 (all unique)
ApplicantId. 1|1|2|2|3|3|3|4|1|2
GroupId..... 5|6|5|8|5|6|8|5|6|7
Status....... n|y|y|n|y|y|m|y|m|n

I want to return all GroupId's that do not contain Applicants with status m belonging to them. So GroupId 6 and 8 should be excluded from the results. Someone suggested I use a subquery with NOT EXISTS, any ideas? (the table has around 30,000 rows)

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 07:02:27

maybe you can use


in([Another Query])

it might help..

A maze make you much more better

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 07:18:49

Your sample data doesn't match your expected output.

DECLARE	@Sample TABLE
	(
		RoundID INT,
		ApplicantID INT,
		GroupID INT,
		[Status] CHAR(1)
	)

INSERT	@Sample
SELECT	 1, 1, 5, 'n' UNION ALL
SELECT	 2, 1, 6, 'y' UNION ALL
SELECT	 3, 2, 5, 'y' UNION ALL
SELECT	 4, 2, 8, 'n' UNION ALL
SELECT	 5, 3, 5, 'y' UNION ALL
SELECT	 6, 3, 6, 'y' UNION ALL
SELECT	 7, 3, 8, 'm' UNION ALL
SELECT	 8, 4, 5, 'y' UNION ALL
SELECT	 9, 1, 6, 'm' UNION ALL
SELECT	10, 2, 7, 'n'

SELECT	*
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 07:23:02

[code]SELECT DISTINCT
s.GroupID
FROM @Sample AS s
WHERE NOT EXISTS (SELECT * FROM @Sample AS x WHERE x.ApplicantID = s.ApplicantID AND x.[Status] = 'm')[/code]

N 56°04'39.26"
E 12°55'05.63"

iradev
Starting Member

45 Posts

Posted - 2010-07-09 : 07:33:19

thanks everyone, Peso's solution is what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources