I have the following two tables: Items: id price shop_id name 1 10.22 1 apple 2 10.50 1 pear 3 10 2 orange 4 9 2 apricot Shops: id name 1 fruit-shop 2 grocery-shop I am trying to get all the shops that have EVERY item price > 10. I want to retrieve all the items associated with that shop.

SQL join all values whos items price > 10

I have the following two tables:

Items:

id	price	shop_id	name
1	10.22	1	apple
2	10.50	1	pear
3	10	2	orange
4	9	2	apricot

Shops:

id	name
1	fruit-shop
2	grocery-shop

I am trying to get all the shops that have EVERY item price > 10. I want to retrieve all the items associated with that shop.

For example, based on the above tables I would have:

name	item_name	price
fruit-shop	apple	10.22
fruit-shop	pear	10.50

I have tried using the following query:

SELECT items.shop_id
FROM items
GROUP BY items.shop_id
HAVING min(items.price) > 10

This returns back the correct shop_id although then when I try and join and achieve the final goal, all the other shops are also joined and not just shops who’s ALL items price are greater than 10.

How to achieve this?

Answer

SELECT shops.name, items.name AS item_name, items.price
FROM items JOIN shops ON items.shop_id = shops.id
WHERE shops.id IN (SELECT items.shop_id
        FROM items
        GROUP BY items.shop_id
        HAVING min(items.price) > 10);

Advertisement

Answer