Reversing Nested Subqueries - 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 2005 Forums

Transact-SQL (2005)

Reversing Nested Subqueries

Author

Topic

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-08-08 : 14:51:32

Hello

I'm using a subquery shown below. It below works, but the results give fake positives since the clean up bit needs to be running first before the second bit compares the data. The code in the inner parenthesis establishes whether there are any duplicate Registration.URN's. How do I swap the statements around.

SELECT

Registration.SettingName,
Registration.URN,
Registration.RegistrationID

FROM
Registration

WHERE

(Registration.Inactive = 0
AND
Registration.URN = ANY
(SELECT Registration.URN from Registration group by Registration.URN having count(*) > 1))

----The inner portionof the nested subquery uses the count feature to identify duplicates

ORDER BY
Registration.URN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 04:39:20

SELECT r.SettingName,
r.URN,
r.RegistrationID
FROM Registration AS r
INNER JOIN (SELECT URN FROM Registration WHERE Inactive = 0 GROUP BY URN HAVING COUNT(*) > 1) AS x ON x.URN = r.URN

E 12°55'05.25"
N 56°04'39.16"

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-08-10 : 06:14:23

Thanks

This coding seems do do the trick and have expanded out on it.

BarryN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources