Newbie question on forming a query

1

Hi I am new to SQL and just poked around the company database to try and generate a report. I got stumped almost immediately. For the table called order_statuses there is a fields (?) order_id and another called status_id. In the table there will be multiple entries containing the same order_id and they will all have their own status_id. For example:

order_id 12202 status_id 8
order_id 12202 status_id 7
order_id 12202 status_id 2
order_id 12202 status_id 14
order_id 12203 status_id 4
order_id 12203 status_id 2
order_id 12203 status_id 12
order_id 12203 status_id 9
order_id 12203 status_id 2
order_id 12204 status_id 5
order_id 12204 status_id 2
order_id 12204 status_id 11
order_id 12204 status_id 3
order_id 12204 status_id 9

What I want to do is extract all the order_ids where they have no status_id of 8 AND also no status ID of 4

(8 means paid and 4 means canceled.....so if the order isn't paid and isn't canceled that means we need to get paid so I'd like to get a list of those orders (order_id) where that's the case)

Is this easy or hard? Do I have to first dump a query into another table and then run a query on that? I am supernoob here so ANY help would be greatly appreciated.

thanks!

Serge

2

Easy:

SELECT order_id /*, MIN(status_id) AS status_id_min, MAX(status_id) AS status_id_max) */
FROM table_name
GROUP BY order_id
HAVING MAX(CASE WHEN status_id IN (4, 8) THEN 1 ELSE 0 END) = 0
1 Like