4

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.

  1. I'm looking for a query to find whatever the triplet may be. I won't necessarily know what it is in the data.
  2. 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.

0

1 Answer 1

7

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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