SQL: select all users for whom all orders meet a condition-CodePudding

I have two tables, one with users and one with orders (I can join the tables on user_id). I'd like to identify users for whom ALL of their orders meet a set of conditions (say, status = "shipped"). For example, for the data below I'd want the query to return only user 2. What's the best way of doing this?

users:

user_id	us_state
1	CA
2	MI
3	TX
4	NY

orders:

order_id	status	user_id
a	"pending"	1
b	"shipped"	1
c	"shipped"	2
d	"shipped"	2
e	"pending"	3
f	"pending"	1

I tried queries like the following but I don't think that's doing the right thing:

SELECT user_id
FROM orders
WHERE user_id = ALL (
  SELECT user_id
  FROM orders
  WHERE status = 'shipped'
);

CodePudding user response：

One solution may be to get the count of status = 'shipped' and status <> 'shipped' for each user and see if the count of status <> 'shipped' is 0. This may not be the best solution, but it works.

SELECT user_id
FROM (
    SELECT u.user_id, 
    SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS count_shipped,
    SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS count_pending
    FROM users u
    JOIN orders o on o.user_id = u.user_id
    GROUP BY u.user_id
) foo
WHERE foo.count_pending = 0;

CodePudding user response：

The ALL operator:

returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.

--Select    * From  (
    SELECT Distinct us.user_id
    ,       us.us_state
    ,       Count(IIF(ord.status = 'shipped',1,Null)) over (partition by us.user_id) AS shippedCount
    ,       Count(IIF(ord.status = 'pending',1,Null)) over (partition by us.user_id) AS pendingCount

    FROM users  us
    JOIN orders ord 
    on ord.user_id = us.user_id
    Where us.user_id=2 --)Tmp Where pendingCount=0

You can remove the comment symbols from the code (--)