question

red68 avatar image
red68 asked

Query to see if 2 or more persons share the same userid in SQL Server

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!

sqlserver2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

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
;


;

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

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.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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));

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.