SQL Server – How to Find Common Values Among Rows

sql server

I have the following table structure with example values:

Number      Reference                 Length                                  Quantity
----------- ------------------------- --------------------------------------- ----------
16002983    ASH 14                    383.000000                              4
16002983    ASH 14                    1669.000000                             4
16002983    GLAS-NP                   403.000000                              1
16003002    SZR 16                    1402.000000                             1
16003002    Energy Surcharge F        56556.680000                            1
16003256    SZR 14                    726.000000                              1
16003256    Energy Surcharge F        40706.820000                            1
16003256    7283-ASB                  726.000000                              1
16003308    ASH 14                    740.000000                              4

The table has, as of now, 742 entries and expands every day.

I need to find a Reference that is shared among all Numbers, if there is any.

How? I don't even know if I need JOINs, GROUP BY, something else? I seem to fail at logic here.

Any help appreciated.

Best Answer

Group by reference, count distinct numbers in each group and compare that to the total count of distinct numbers in the table:

SELECT
  Reference
FROM
  dbo.atable
GROUP BY
  Reference
HAVING
  COUNT(DISTINCT Number) = (SELECT COUNT(DISTINCT Number) FROM dbo.atable)
;
Related Question