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?

Glitch Desire · Accepted Answer · 2013-08-09 15:36:33Z

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 9, 2013 at 15:36

Glitch Desire

15.1k7 gold badges46 silver badges55 bronze badges

answered Aug 9, 2013 at 15:28

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

aldux Over a year ago

You can achieve the same results without the complexity. See LD answer.

Bohemian Over a year ago

+1 correct (but aweful formatting). I have no idea why someone downvoted it.

valex Over a year ago

@aldux: It is just I want to get all distinct As that do not have a B of 2 or 3 translated into SQL :)

Mihai Over a year ago

Maybe i don't get it,but distinct A where B is not 2 or 3 wont return 1,2,3?

valex Over a year ago

@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.

|

Nikhil Doomra · Accepted Answer · 2013-08-09 16:09:19Z

0

try to use the following code:

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

answered Aug 9, 2013 at 16:09

Nikhil Doomra

3983 silver badges9 bronze badges

1 Comment

idle Over a year ago

This would still return 1 for A because in the first row of A B is neither 2 nor 3 …

Collectives™ on Stack Overflow

Select A Value Given Multiple Rows

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related