I’ve been out of the SQL game for a while now and am trying to get back into it. I have some data I’m trying to find matching groups of. I’m not even really sure how to describe what I’m looking for.
I have two data points. Teams and people. Each team has one or more people in it, and I want to find any teams that have the same people in them.
Column 1
Column 2
X
Adam
X
Dana
X
Fiona
Y
Adam
Y
Becky
Y
Carl
Y
Eric
Z
Adam
Z
Becky
Z
Carl
Z
Eric
We can query the data and get the following.
Teams
Adam
Becky
Carl
Dana
Eric
Fiona
X
1
1
1
Y
1
1
1
1
Z
1
1
1
1
We can see that team Y and Z are the same. There are hundreds of teams and a thousand plus people so I’m trying to build a query that will more readily identify the teams that match each other.
DROP TABLE IF EXISTS #;
CREATE TABLE # (
team CHAR (1) NOT NULL,
member VARCHAR (32) NOT NULL,
PRIMARY KEY (team, member)
);
INSERT #
VALUES ('X', 'Adam'),
('X', 'Dana'),
('X', 'Fiona'),
('Y', 'Adam'),
('Y', 'Becky'),
('Y', 'Carl'),
('Y', 'Eric'),
('Z', 'Adam'),
('Z', 'Becky'),
('Z', 'Carl'),
('Z', 'Eric');
WITH checks
AS (SELECT team,
CHECKSUM_AGG(binary_checksum(member)) AS check_
FROM #
GROUP BY team),
ties
AS (SELECT check_
FROM checks
GROUP BY check_
HAVING count(*) > 1)
SELECT b.team as identical_teams
FROM ties AS a
INNER JOIN
checks AS b
ON a.check_ = b.check_;
It shows they are identical, but if there’s more than 3 groups it won’t pair them up (A=B, C=D, but A<>C). CHECKSUM_AGG() might not be the best way to determine group uniqueness.
I might have a better variation that does identify them that way but not sure I’ll get to it tonight.
DROP TABLE IF EXISTS #teams;
CREATE TABLE #teams ( team varchar(10) NOT NULL, person varchar(50) NOT NULL );
INSERT INTO #teams VALUES
('X', 'Adam '),
('X', 'Dana '),
('X', 'Fiona'),
('Y', 'Adam '),
('Y', 'Becky'),
('Y', 'Carl '),
('Y', 'Eric '),
('Z', 'Adam '),
('Z', 'Becky'),
('Z', 'Carl '),
('Z', 'Eric ')
DROP TABLE IF EXISTS #team_member_counts;
CREATE TABLE #team_member_counts ( team varchar(10) NOT NULL, member_count int NULL );
INSERT INTO #team_member_counts
SELECT team, COUNT(*)
FROM #teams
GROUP BY team
SELECT tmc1.team, t2.team, tmc1.member_count, t2.matching_members
FROM #team_member_counts tmc1
CROSS APPLY (
SELECT t2.team, COUNT(*) AS matching_members
FROM #team_member_counts tmc2
INNER JOIN #teams t2 ON t2.team = tmc2.team
INNER JOIN #teams t1 ON t1.team = tmc1.team AND t1.person = t2.person
WHERE tmc2.team > t1.team AND tmc2.member_count = tmc1.member_count
GROUP BY t2.team
) AS t2
WHERE tmc1.member_count = t2.matching_members
was able to do "the same thing" using 2D array and looping
consistent
3D array
REGEX
to do "the same thing"
* Co Pilot = data given = asked answer
* correct output given
* Co pilot = My Query gibberish = still correct output
DAWN of a New AGE era
* NO programming language
* No need = Expensive licenses = lots money = support
:wink: :winking_face_with_tongue:
What a nice question. I would be inclined to use STRING_AGG() with more normalized data.
Test Data
[code]
CREATE TABLE #Members
(
member_id int not null
CONSTRAINT PK_Members PRIMARY KEY
,member_name varchar(20) NOT NULL
);
INSERT INTO #Members
VALUES (1, 'Adam'),(2, 'Becky'),(3, 'Carl')
,(4, 'Dana'),(5,'Eric'),(6,'Fiona');
CREATE TABLE #Teams
(
team_id int not null
CONSTRAINT PK_Teams PRIMARY KEY
,team_name varchar(20) NOT NULL
);
INSERT INTO #Teams
VALUES (1, 'X'),(2, 'Y'),(3, 'Z');
CREATE TABLE #TeamMembers
(
team_id int not null
,member_id int not null
,CONSTRAINT PK_TeamMembers PRIMARY KEY (team_id, member_id)
);
INSERT INTO #TeamMembers
VALUES (1, 1),(1, 4),(1, 6),(2, 1),(2, 2),(2, 3)
,(2, 5),(3, 1),(3, 2),(3, 3),(3, 5);
[/code]
Try something like:
[code]
WITH MemberGroups
AS
(
SELECT team_id
,STRING_AGG(CAST(member_id AS varchar(MAX)), '-')
WITHIN GROUP (ORDER BY member_id) AS MemberGroup
FROM #TeamMembers
GROUP BY team_id
)
,MultipleGroups
AS
(
SELECT MemberGroup
FROM MemberGroups
GROUP BY MemberGroup
HAVING COUNT(*) > 1
)
SELECT G.team_id, G.MemberGroup
FROM MemberGroups G
JOIN MultipleGroups M
ON G.MemberGroup = M.MemberGroup;
-- etc
