I have the following two tables:

Items:

Shops:

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:

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?

CodePudding user response：

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);

CodePudding user response：

• It's a 2 step problem:
  1. Group all Items by their shop_id and find the MIN price of all items in that shop.
     • Your query does this already.
  2. Then JOIN those shop_id values with the items that shop has.
     • This needs to be done in an outer-query or CTE.

Like so:

WITH shopsWithAllItemsPricedOver10 (
    SELECT
        items.shop_id
    FROM
        items
    GROUP BY
        items.shop_id
    HAVING
        MIN( items.price ) > 10
)
SELECT
    i.*
FROM
    items AS i
    INNER JOIN shopsWithAllItemsPricedOver10 AS s ON s.shop_id = i.shop_id
ORDER BY
    i.shop_id,
    i.id

CodePudding user response：

Exists logic works nicely here:

SELECT id, name
FROM Shops s
WHERE NOT EXISTS (SELECT 1 FROM Items i
                  WHERE i.shop_id = s.id AND
                        i.price <= 10);

Here is a demo showing that the logic is working.

CodePudding user response：

"I am trying to get all the shops that have EVERY item price > 10. "

Would an INNER JOIN work for you here?

SELECT * FROM Shops s
INNER JOIN Items i
ON s.id = i.shop_id
WHERE i.price > 10

This will return all columns from the Shops table where the Item price is > 10.