Search code examples
sqlms-accessdistinct

SQL Unique rows query in MS Access

I got the following entries:

Col_A  | Col_B  | Col_C
Foo    | Bar    | 1
Foo    | Blub   | 1
Foo    | Bar    | 1
Foo    | Blub   | 2
Foo    | Blub   | 2
Foo    | Bar    | 3
Foo    | Bar    | 3
Foo    | Bar    | 3
Foo    | Blub   | 4
Foo    | Bar    | 5
Foo    | Blub   | 5

I'm search for all Col_C values where in Col_B only contain Blub. Which mean I want to see

Col_A  | Col_B  | Col_C
Foo    | Blub   | 2
Foo    | Blub   | 4

A distinct row where there is no entry Bla in Col_B

currently I also receive

Foo    | Blub   | 1
Foo    | Blub   | 5
Solution

  • Try this:

    SELECT DISTINCT t1.*
FROM your_table t1
WHERE NOT EXISTS
    (SELECT 1 
     FROM your_table t2
     WHERE t2.col_b <> 'Blub'
     AND t1.col_a = t2.col_a
     AND t1.col_c = t2.col_c)
AND t1.col_b = 'Blub'

    See the SqlFiddle