Database Administration
- sql-server t-sql sql-server-2019
- Updated Fri, 23 Sep 2022 07:12:53 GMT
-
SQL: finding unique multi-column value triplet matching multiple rows?
TL;DR: using Transact-SQL/SQL Server, how can I find the rows of a table which match a single "solution" triplet of column values (ColA, ColB, ColC) for some set of values in another column?
More info: Let's say I've got a table similar to this (ID column removed for focus):
| FID | ColA | ColB | ColC |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 1 | 2 | 2 | 2 |
| 2 | 1 | 1 | 1 |
| 2 | 3 | 3 | 3 |
| 3 | 1 | 1 | 1 |
| 3 | 2 | 2 | 2 |
Solution
One way to find the common tuple:
DECLARE @T table
(
FID integer NOT NULL,
ColA integer NOT NULL,
ColB integer NOT NULL,
ColC integer NOT NULL
);
INSERT @T
(FID, ColA, ColB, ColC)
VALUES
(13,1,305,507),
(13,1,415,928),
(17,2,5,57),
(17,1,415,928),
(210,12,305,507),
(210,1,415,928);
SELECT
T.ColA,
T.ColB,
T.ColC
FROM @T AS T
GROUP BY
T.ColA,
T.ColB,
T.ColC
HAVING COUNT_BIG(*) =
(
SELECT COUNT_BIG(DISTINCT T2.FID)
FROM @T AS T2
);