1

I need to find commonality across 3 columns of data. For instance, user 1 and user 2 have the same email. user 2 and user 3 have the same device id. So I want to assign a unique parentuserid of 1 to user 1, 2, and 3. See below for sample data.

DROP TABLE IF EXISTS #usr
SELECT 'u1' AS userid, 'email1' AS email, 'device1' AS device INTO #usr UNION ALL
SELECT 'u2' AS userid, 'email1' AS email, 'device2' AS device UNION ALL
SELECT 'u3' AS userid, 'email99' AS email, 'device2' AS device UNION ALL
SELECT 'u4_nomatch' AS userid, 'email77' AS email, 'device77' AS device  
 


-- expected results
-- new parentuserid    userid
--  1                u1
--  1                u2
--  1                u3
--  2                u4_nomatch

This parentuserid = 1 is a set of 3 users, but this could actually be n (50+). I thought maybe cross join with window functions would be needed here or maybe recursive CTE but I haven't had any luck. It's hard to Google this sort of problem.

SQL Server 2017

1

These users form a graph. They are linked by edges to common attributes (email, device etc.). The question boils down to finding disjoint subgraphs within the data, and assigning each a label.

There's an answer on SO which addresses this. The web will have many more if you care to search.

SQL Server 2019 introduced SHORTEST_PATH() which would simplify the query.

| improve this answer | |
  • Thanks, that points me in the right direction! – Gabe Jun 26 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.