Group by x where y = A and B and C

Question

I have a table that stores product price information. It has a column for the product ID, a quantity and a discount. (Users buys 2 gets £1 off, buys 3 gets £2 off etc).

I'd like to pull all products that have specific quantity discounts associated with them. For example only products that have £5, £10 and £20 off buying 1,2 or 3 respectively.

How can I do that?

I want to group by product ID where there is are three rows that match my criteria - and no other products.

Answer 1

Not tested (depends on your schema) but here's the idea:

SELECT P.id, count(P.id)
FROM products P
  LEFT JOIN discounts D ON P.id = D.product_id
WHERE
  (D.quantity = 1 AND D.amount = 5)
  OR (D.quantity = 2 AND D.amount = 10)
  OR (D.quantity = 3 AND D.amount = 20)
GROUP BY P.id
HAVING count(P.id) = 3

Answer 2

This is the query for SQL Server, there might be a couple tweaks for MySql, but this is the basics.

SELECT * FROM <TABLE> WHERE ProductId in
(SELECT ProductId
FROM <TABLE>
GROUP BY ProductId
HAVING COUNT(ProductId) = 3)