CODEWITHSUNDEEP
phpmysqlsql
Adam Dempsey
Adam Dempsey

Reputation: 2962

SQL Select multiple matching rows

I have a SQL database of products from different suppliers, so the same product could appear multiple times with different prices.

Is it possible to select all of the products that have more than 4 prices, so bascially all of the rows which have more than 4 rows with the same ID?

Upvotes: 1

Views: 1742

Answers (3)

rid
rid

Reputation: 63442

You could add COUNT(*) AS number_of_products to SELECT, GROUP BY product_id, then use HAVING number_of_products > 4.

Note that HAVING is applied on the results (it basically goes through all the results, one by one, and applies the conditions), so it will be slower than WHERE. If you have hundreds of thousands of rows and you need performance, consider pre-counting the products, storing the indexed count value somewhere, then using a simple WHERE instead.

Upvotes: 2

TBohnen.jnr
TBohnen.jnr

Reputation: 5119

This is the perfect thing for a group by:

SELECT ProductID, COUNT(*) AS PriceCount 
FROM Product GROUP BY
Product,Price HAVING COUNT(Product) > 4

Upvotes: 0

cairnz
cairnz

Reputation: 3957

Yes, GROUP on the identificator for your item, and specify the number of prices to count in a HAVING clause, something like this :

SELECT ItemID, COUNT(Price) FROM itemTBL GROUP BY ItemID HAVING COUNT(Price) >= 4

You can then use this to later filter and get more information:

SELECT Item.*, Category.Name, Filter.NumPrices from itemTBL AS Item 
INNER JOIN categoryTBL as Category ON Item.CategoryID = Category.CategoryID
INNER JOIN (SELECT ItemID, COUNT(Price) AS NumPrices FROM itemTBL GROUP BY ItemID HAVING COUNT(Price) >= 4) AS Filter on Item.ItemID = Filter.ItemID

Upvotes: 1

Related Questions