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.
