I have a database with multiple items in it. Some sample entries from the database include:
I would like to retrieve only the items with multiple prices. So I want the table to be returned to look like:
I don't want the items that just have one price, just the ones with multiple prices and all their different prices.
I know that I can do this with a script and several SQL queries, but I'm wondering if there is a way I could do this with just a SQL query and no scripting. I'm still learning SQL, so I don't know all the tricks yet. If it's not possible to do with just one SQL query please tell me so, I'm starting to think it's impossible.
You can use the following query:
SELECT name, price FROM Items WHERE name IN ( SELECT name FROM Items GROUP BY name HAVING MIN(price) <> MAX(price))
You could create a subquery containing the items with multiple prices, and filter the original list of items by inner-joining to the subquery, e.g.
SELECT Items.* FROM Items INNER JOIN (SELECT name FROM Items HAVING COUNT(DISTINCT price) > 1) multiPriceItems ON Items.name = multiPriceItems.name
SELECT DISTINCT x.* FROM items x JOIN items y ON y.name = x.name AND y.price <> x.price