2

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.

5
  • do you need for each reference,count of number – TheGameiswar Mar 17 '16 at 9:39
  • The Number column is a unique identifier. I'd like to have just one result - the Reference that occurs at least once per every different Number. – ROAL Mar 17 '16 at 9:43
  • Not clear to me – paparazzo Mar 17 '16 at 9:52
  • 2
    Number cannot possibly be a unique identifier in this table since it can have duplicate values, as follows from your question. – Andriy M Mar 17 '16 at 9:59
  • 1
    @AndriyM you are right, I used a wrong term. Number serves as a reference to another table, together with two more columns which are omitted here. – ROAL Mar 17 '16 at 10:07
5

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)
;
1
  • 1
    Simple, straightforward, clean. I like this very much, thank you. – ROAL Mar 17 '16 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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