I have a user profile table with user id's and person's names. I won't to query the table to see if multiple users share the same user id. Ex: John Doe with User ID: 123; James Test with User ID: 123. Show me these occurrences grouped by User ID. Thanks!
Would the following do it for you? I ask because it only does one scan of the table instead of 3. It's what Thomas Rushton was suggesting.
SELECT User_ID, DupeCount = COUNT(*) FROM dbo.UserProfiles GROUP BY User_ID HAVING COUNT(*) >= 2 ;
;
This reads like homework, so I'm not going to give you an answer...
...but I suggest you look at the "HAVING" part of a "GROUP BY" clause.
If it's not homework, I suggest you then go and fix your table so there's a UNIQUE constraint on the USER ID field.
Not homework...I inherited this database from a previous co-worker but I figured it out. Here is the script in case anyone else run's into this issue. Once issue is resolved, I will definitely add a UNIQUE constraint. Thanks!
SELECT distinct user_id FROM UserProfiles
WHERE user_id IN (SELECT DISTINCT user_id FROM UserProfiles a WHERE EXISTS (SELECT * FROM UserProfiles b WHERE b.user_id=a.user_id AND b.name<>a.name));
16 People are following this question.
