IN failing with subquery! - 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)

IN failing with subquery!

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2010-01-19 : 09:27:52

I have a subquery that pulls the correct records out. I want to then get the full rows from the original table so am using IN. Its not working though! Any ideas?


SELECT MAX(dbo.Attendees.UniqueID) AS UniqueId
	FROM dbo.Attendees 
		LEFT OUTER JOIN dbo.Attendees_Courses
			ON dbo.Attendees_Courses.AttendeeId = Attendees.UniqueID
	GROUP BY Attendees.UniqueId
	HAVING(
		COUNT(dbo.Attendees_Courses.UniqueID) = 0
	)

returns


4496
4499

but


SELECT * FROM Attendees WHERE UniqueId IN(
	SELECT MAX(dbo.Attendees.UniqueID) AS UniqueId
	FROM dbo.Attendees 
		LEFT OUTER JOIN dbo.Attendees_Courses
			ON dbo.Attendees_Courses.AttendeeId = Attendees.UniqueID
	GROUP BY Attendees.UniqueId
	HAVING(
		COUNT(dbo.Attendees_Courses.UniqueID) = 0
	)
)

returns no rows! What am I missing?

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:12:03

If you have cut & pasted the code (rather than retyping) then either a) nothing

or possibly b) a different "owner" for the table in the outer select:


SELECT * FROM dbo.Attendees WHERE UniqueId IN( ...

By the by, the MAX is redundant, you are grouping on the UniqueID, no need for a MAX as well.

Would a NOT EXISTS do here?


SELECT * 
FROM dbo.Attendees AS A
WHERE NOT EXISTS
(
    SELECT * 
    FROM dbo.Attendees_Courses AS C
    WHERE C.AttendeeId = A.UniqueID
)

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2010-01-19 : 10:26:05

Many Thanks Kristen, Spot on! the missing dbo was the culprit :)

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:31:34

You've got two copies of that table then (in case you weren't aware of that and its a potential "shouldn't be"

issue).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources