SQL query for grouping data from two tables

Question

I have 3 tables:

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

At first I wanted to list all users with their respective count of orders like this:

• A, 1 order
• B, 5 orders

That's easy, I'm doing a "select name, count(id) from users, orders where users.id = orders.userId group by name".

Now, I'd like to further filter this data to show only users with orders that have items with status = "unprocessed". I am not sure how to go about grouping data from 2 tables. In the end, I'm looking to get data like:

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

Thanks!

Answer 1

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"

I'm testing this now, not quite sure if I have it right. Edit: seems good.

Answer 2

Use:

  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 

Using a JOIN to ORDERS_ITEMS would require the use of DISTINCT in the 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 

...because of row duplication when there are more than one unprocessed item to an order...