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

I'm trying to find the common triplet of values for (ColA, ColB, ColC) that exists for each value of FID. In this example, (1, 1, 1) would be that triplet, so I'd like to return something like:

FID
ColA
ColB
ColC

1
1
1
1

2
1
1
1

3
1
1
1

This feels like it should be some kind of set operation using intersect but I'm stumped in how best to model and code this up declaratively. Thoughts? And many thanks in advance for your help!

Clarification: A few points that hopefully will help explain my question.

• I'm looking for a query to find whatever the triplet may be. I won't necessarily know what it is in the data.

• The triplet values are not dependent on the FID value, and vice versa. My example just happened to use (1,1,1) which matched one of the FID values. The data could look more like:

FID
ColA
ColB
ColC

13
1
305
507

13
1
415
928

17
2
5
57

17
1
415
928

210
12
305
507

210
1
415
928

So in this case, I'm looking for how to write the query that would find out that (1,415,928) is the triplet common to all FIDs.

Hopefully that helps better frame my question and what I'm looking for :) Please let me know if you need more info.

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

ColA
ColB
ColC

1
415
928

To return all matching tuples:

SELECT 
    T.FID, 
    T.ColA, 
    T.ColB, 
    T.ColC
FROM @T AS T
WHERE EXISTS
(
    SELECT T.ColA, T.ColB, T.ColC
    INTERSECT
    SELECT T2.ColA, T2.ColB, T2.ColC 
    FROM @T AS T2
    WHERE T2.FID <> T.FID
)
AND NOT EXISTS
(
    SELECT T.ColA, T.ColB, T.ColC
    EXCEPT
    SELECT T3.ColA, T3.ColB, T3.ColC 
    FROM @T AS T3
    WHERE T3.FID <> T.FID
);

or

SELECT
    T1.FID, T2.ColA, T2.ColB, T2.ColC
FROM
(
    SELECT T2.FID, rc = COUNT_BIG(*) OVER ()
    FROM @T AS T2
    GROUP BY T2.FID
) AS T1
JOIN 
(
    SELECT T.ColA, T.ColB, T.ColC, rc = COUNT_BIG(DISTINCT T.FID)
    FROM @T AS T
    GROUP BY T.ColA, T.ColB, T.ColC
) AS T2
    ON T2.rc = T1.rc;

both give:

FID
ColA
ColB
ColC

13
1
415
928

17
1
415
928

210
1
415
928

db<>fiddle

StackExchange Database Administrators Q#315564, answer score: 7