Select rows depending on multiple values SQL

Question

I need help to write this in SQL:

I have one table and I would like to select No with Chars A, B or C, but if I have A and B for same No I want to show only B, else I need to show all.

Pseudo code:

SELECT *
  FROM Table1
  WHERE IF EXISTS (Char = A and Char = B)
    THEN Char IN (B, C)
    ELSE Char IN (A, B, C)

Example:

Table1

ID  No     Char
1   10     A
2   10     B
4   10     C
5   11     A
6   11     D
7   12     C

I want result:

ID  No     Char
2   10     B
4   10     C
5   11     A
7   12     C

Is it clear enough what I need?

Raphaël Althaus · Accepted Answer · 2012-05-31 15:54:48Z

3

select t1.Id, t1.No, t1.Ch from Table1 t1
where t1.Ch IN ( 'B', 'C')
UNION
select t1.Id, t1.No, t1.Ch from Table1 t1
where t1.Ch ='A' 
and not exists (select null from Table1 t2 where t2.No = t1.No  and t2.Ch='B')

I changed Char in Ch as Char might be a reserved KeyWord...

Tested with Sql Server, but the query is, I think, ANSI SQL

http://sqlfiddle.com/#!3/f2145/12

or

select t1.Id, t1.No, t1.Ch from Table1 t1
where t1.Ch IN ( 'B', 'C')
OR (t1.Ch ='A'
and not exists (select null from Table1 t2 where t2.No = t1.No  and t2.Ch='B'))

edited May 31, 2012 at 15:54

answered May 31, 2012 at 15:49

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

2 Comments

Erwin Brandstetter Over a year ago

Both work work PostgreSQL, second is the better one. (Why not test with PostgreSQL on sqlfiddle?)

Raphaël Althaus Over a year ago

Because I'm quite sure about ANSI SQL, but not so sure about PostgreSQL types (bad argument... I know, I know) ;)

Collectives™ on Stack Overflow

Select rows depending on multiple values SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related