CODEWITHSUNDEEP
sql
user828647
user828647

Reputation: 515

Writing SQL query: slightly complicated

I have a table with primary key ID and two columns Order and Status. The value for Status could be any of 'A', 'P', 'S', 'O' or 'C'. I need to to find the values of Order for which the status is only 'A' or 'C'. How should I write an SQL query that does that? I know some basic SQL but I am unable to get my results using that.

Sample table:

ID  Order   Status

1   1234    A

2   2343    P

3   4351    S

4   8675    C

5   9867    A

6   9867    C

7   1234    A

8   2343    A

Expected result:

1234

8675

9867

Upvotes: 0

Views: 69

Answers (2)

roman
roman

Reputation: 117475

select distinct t.[Order]
from Table1 as t
where
    not exists
    (
         select * from
         Table1 as t2 where t2.[Order] = t.[Order] and t2.Status not in ('A', 'C')
    )

or 

select t.[Order]
from Table1 as t
group by t.[Order]
having
    sum(case when [Status] in ('A', 'C') then 1 else 0 end) = count(*)

or

select distinct t.[Order]
from Table1 as t
where
    t.[Order] not in (select t2.Order from Table1 as t2 where t2.Status not in ('A', 'C'))

see sql fiddle example for details

BTW, Order is not the best name for column, avoid using keywords as you column names

Upvotes: 4

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

With many SQL databases, it's fairly simple using EXCEPT (in Oracle, MINUS)

SELECT [Order] FROM Table1
EXCEPT
SELECT [Order] FROM Table1 WHERE Status NOT IN ('A','C')

An SQLfiddle

In MySQL (notably not having MINUS), you'll have to go for a more classical;

SELECT DISTINCT `Order` FROM Table1 WHERE `Order` NOT IN 
    (SELECT `Order` FROM Table1 WHERE `Status` NOT IN ('A','C'))

Upvotes: 4

Related Questions