CODEWITHSUNDEEP
sqlsubquerywhere-in
user4242750
user4242750

Reputation: 197

SQL Sub-query (Where In)

I get an error with my sub-query and am not seeing what I am doing wrong. Sub-query works on it's on. There Where-In is obviously what is the problem. Also tried EXISTS.

select [ID NUMBER], [PERNO], [TITLE], [INITIALS], [SURNAME], [DATE OF BIRTH] 
from dbo.[DATASEPT002] 
WHERE [ID NUMBER] IN 
( SELECT [ID NUMBER], COUNT([PERSALNO]) AS COUNTOF 
FROM [dbo].[DATASEPT] 
GROUP BY [ID NUMBER] HAVING COUNT([PERSALNO]) >1 )

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have two columns in the subquery. Only one can be used for the IN comparison:

select [ID NUMBER], [PERNO], [TITLE], [INITIALS], [SURNAME], [DATE OF BIRTH] 
from dbo.[DATASEPT002] t
WHERE [ID NUMBER] IN (SELECT [ID NUMBER]
                      FROM [dbo].[DATASEPT] 
                      GROUP BY [ID NUMBER] 
                      HAVING COUNT([PERSALNO]) > 1
                     );

However, I would expression this more typically using window functions:

select t.*
from (select t.*, count(*) over (partition by persalno) as cnt
      from DATASEPT002 t
     ) t
where cnt > 1;

Upvotes: 1

Related Questions