Select A Value Given Multiple Rows

Question

This is a very basic query (related to this one) I can't figure out …

Let's say I have a two column table like this:

I want to get all distinct As that do not have a B of 2 or 3. Using the above example, the only results I want returned is As 2 and 3. How do I do this?

score 4 · Accepted Answer · edited Aug 09 '13 at 15:36

4

SELECT DISTINCT `A` FROM `t` AS `t1`
    WHERE NOT EXISTS (
        SELECT 1 FROM `t` 
            WHERE `t`.`A` = `t1`.`A` 
            AND `B` in (2,3)
    );

SQL Fiddle demo

edited Aug 09 '13 at 15:36

Glitch Desire

14,632
7
43
55

answered Aug 09 '13 at 15:28

valex

23,966
7
43
60

You can achieve the same results without the complexity. See LD answer. – aldux Aug 09 '13 at 15:30
+1 correct (but aweful formatting). I have no idea why someone downvoted it. – Bohemian Aug 09 '13 at 15:32
1

@aldux: It is just `I want to get all distinct As that do not have a B of 2 or 3` translated into SQL :) – valex Aug 09 '13 at 15:33
Maybe i don't get it,but distinct A where B is not 2 or 3 wont return 1,2,3? – Mihai Aug 09 '13 at 15:37
2

@Mihai: NOT "A where B..." but "As that do not have a B...". It makes difference. For A=1 THERE are B=2 and 3. – valex Aug 09 '13 at 15:40
This answer saved my day … :) – idle Aug 09 '13 at 16:33

score 0 · Answer 2 · answered Aug 09 '13 at 16:09

0

try to use the following code:

select distinct a
from tbl
where B not IN(2,3)

answered Aug 09 '13 at 16:09

Nikhil Doomra

398
2
9

This would still return 1 for A because in the first row of A B is neither 2 nor 3 … – idle Aug 09 '13 at 16:26

Select A Value Given Multiple Rows

2 Answers2