SQL query to group data from two tables

Question

I have 3 tables:

• Users (id, name)
• Orders (id, userId)
• Orders_Items (id, orderId, status)

First, I wanted to list all users with the corresponding number of such orders:

• A, 1 order
• B, 5 orders

It's easy, I do "select name, count (id) from users, orders, where users.id = orders.userId group by name".

Now I would like to additionally filter this data to show only users with orders who have items with status = "unprocessed." I am not sure how to collect data from two tables. In the end, I want to get data like:

• (A is not shown, there is no order with any element having status = unprocessed)
• B, 3 orders (2 orders do not have an item with status = unprocessed).

Thank!

Answer 1

:

  SELECT u.name,
         COUNT(o.id) AS numOrders
    FROM USERS u
    JOIN ORDERS o ON o.userid = u.id
   WHERE EXISTS(SELECT NULL 
                  FROM ORDERS_ITEMS oi 
                 WHERE oi.orderid = o.id
                   AND oi.status = 'unprocessed')
GROUP BY u.name 

JOIN ORDERS_ITEMS DISTINCT COUNT:

  SELECT u.name,
         COUNT(DISTINCT o.id) AS numOrders
    FROM USERS u
    JOIN ORDERS o ON o.userid = u.id
    JOIN ORDERS_ITEMS oi ON oi.orderid = o.id
                        AND oi.status = 'unprocessed'
GROUP BY u.name 

... - , ...

Answer 2

select name, count(users.id) from users 
left join orders 
on users.id=orders.userId 
left join orders_items 
on orders.id=orders_items.orderId 
where orders_items.status="unprocessed"

, , . : .