CODEWITHSUNDEEP
sqlselect
user2544102
user2544102

Reputation: 91

SQL Select records excluding some statuses

I'm totally stuck on how to create this select. I need to select from the status table only those order_id's which to not have status 2.

Here is the table:

+----+---------+---------+--
| id | order_id| status  |
+----+---------+---------+--
|  1 |       1 |       1 | 
|  2 |       1 |       2 | 
|  3 |       2 |       1 | 
|  4 |       2 |       2 |
|  5 |       3 |       1 |
|  1 |       3 |       3 | 
|  2 |       4 |       2 | 
|  3 |       4 |       1 | 
|  4 |       4 |       2 |
|  5 |       5 |       3 | 
+----+---------+----------+--

So he select result will be only order_id = 5

Please help!

If you want to include orders with status 1 and exclude those with status 3, then you can use a similar idea:

having sum(case when status_id = 1 then 1 else 0 end) > 0 and
       sum(case when status_id = 3 then 1 else 0 end) = 0

EDIT: I like to EXCLUDE those order_id's:
- Which has only status 1 (not status 2)
- and
- which has status 3

Lets have table like this: 

id--order-id-Prod---Status    
------------------------------  

1   1        a      1  
6   1        b      2  
7   1        a      2  
8   1        b      1  
9   2        a      1  
10  3        a      1  
11  3        b      1  
12  3        a      2  
13  3        b      2  
14  4        a      1  
15  4        b      1  
16  5        a      1  
17  5        b      1  
18  5        a      2  
19  5        b      2  
20  5        a      3  
21  5        b      3

Select should show only order_id "5"

Upvotes: 2

Views: 1083

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is an example of a set-within-sets query:

select order_id
from t
group by order_id
having sum(case when status = 2 then 1 else 0 end) = 0

The having clause counts the number of rows with a status of 2. The = 0 finds the orders with no matches.

EDIT:

If you want to include orders with status 1 and exclude those with status 3, then you can use a similar idea:

having sum(case when status_id = 1 then 1 else 0 end) > 0 and
       sum(case when status_id = 3 then 1 else 0 end) = 0

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Another way is to use EXCEPT:

SELECT order_id
FROM StatusTable

EXCEPT

SELECT order_id
FROM StatusTable
WHERE status = 2;

It works in SQL-Server and Postgres (and in Oracle if you replace the EXCEPT with MINUS.)

Upvotes: 1

Robin
Robin

Reputation: 2616

I think this works, one query to select all ids, one to get those with a status of 2 and left joining on order_id and picking those with null order_id in the list of orders with a status of 2.

select 
  all_ids.order_id
from 
(
  select distinct
    order_id
  from status
) all_ids
left join
(
  select 
    order_id
  from status
  where status = 2
) two_ids
on all_ids.order_id = two_ids.order_id
where two_ids.order_id is null

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Here's one way.

Select * from TableName 
where Order_ID not in (Select order_ID from tableName where status=2)

Another way would be to use the not exists clause.

Upvotes: 3

Related Questions