How to select rows where for same `userid` other field has specific values?

Question

I have this kind of table (simplified):

orders           sample data below
---------------------------------------------
id INT:          1     2     3     4     5
userid INT       10    10    10    20    20
status CHAR(1)   A     A     B     A     C

and want to select all orders where for each userid status is IN ('A','B') but have no orders at all IN ('C','D').

So output for above data would give orders with ID=1, 2 and 3. User ID=10 have orders A and B, but no C or D.

In other words: Select orders for customers who have orders with status A or B, but none of statuses C or D.

I started with this:

SELECT 
    xcart_orders.orderid, 
    xcart_orders.*          
    FROM xcart_orders 
    JOIN (
        select count(*) as bad_statuses, userid from xcart_orders 
        where status in ('C','D') 
        group by userid 
    ) bo 
    ON bo.userid=xcart_orders.userid 
    JOIN (
        select count(*) as good_statuses, userid from xcart_orders 
        where status in ('A','B') 
        group by userid 
    ) bo2 
    ON bo2.userid=xcart_orders.userid 
    WHERE bo2.good_statuses>0 and bo.bad_statuses=0  

but think count(*) won't return zero for 'bad' statuses, so I get no results.

Answer 1

You have an aggregation without GROUP BY and for check the result you need us HAVING instead of WHERE

SELECT 
    xcart_orders.orderid, 
    xcart_orders.*, 
    SUM(CASE WHEN xcart_orders.status in ('C','D') THEN 1 ELSE 0 END) AS bad_statuses, 
    SUM(CASE WHEN xcart_orders.status in ('A','B') THEN 1 ELSE 0 END) AS good_statuses 
FROM xcart_orders 
GROUP BY orderid
HAVING bad_statuses = 0 
   AND good_statuses > 0 

Please be aware the fields you get from xcart_orders.* will be random (or non deterministc) if you need a particular one you need to order it first.

• First you GROUP BY user_id to check if have any status different to 'A', 'B'

• Then you select orders from those user_id:

SQL DEMO

SELECT `user_id`
FROM orders1
GROUP BY `user_id`
HAVING COUNT(*) = COUNT(CASE WHEN `status` IN ('A', 'B') THEN 1 END);


SELECT *
FROM orders1
WHERE `user_id` IN (SELECT `user_id`
                    FROM orders1
                    GROUP BY `user_id`
                    HAVING COUNT(*) = COUNT(CASE WHEN `status` IN ('A', 'B') THEN 1 END)
                   );

OUTPUT