So I'm drawing a blank here... it's Monday! I have two tables - cart and cart_items.
I would like to select all carts and the cart_items which have been fully invoiced (where cart_items.quantity_invoiced = quantity). If a cart has 3 items, and all 3 items have quantity_invoiced = quantity then I want the three line items to show up in the query results. If a cart has 3 items and only two items have quantity_invoiced = quantity (the third item does not match), I do not want this cart or any of its cart_items to show up. Does that make sense? I'm drawing a blank on how to write this query. Can anyone help?
Format of results should be something similar to this:
cart_id | date_ordered | item_name | quantity | price | (qty * price)
Where I'm drawing the blank is excluding ALL carts from results in which any cart_item record does not match (quantity_invoiced = quantity).
FROM ( SELECT cart_id
HAVING 0 =
COUNT(CASE WHEN quantity <> quantity_invoiced
THEN 'uh oh'
) AS ok
JOIN carts AS c
ON c.id = ok.cart_id
JOIN cart_items AS ci
ON ci.cart_id = c.id
Thanks! The HAVING clause is what I completely forgot about and was drawing a blank on.