MySQL: Return grouped fields where the group is not empty, effeciently
- by Ryan Badour
In one statement I'm trying to group rows of one table by joining to another table. I want to only get grouped rows where their grouped result is not empty.
Ex. Items and Categories
SELECT Category.id
FROM Item, Category
WHERE Category.id = Item.categoryId
GROUP BY Category.id
HAVING COUNT(Item.id) > 0
The above query gives me the results that I want but this is slow, since it has to count all the rows grouped by Category.id.
What's a more effecient way?
I was trying to do a Group By LIMIT to only retrieve one row per group. But my attempts failed horribly. Any idea how I can do this?
Thanks
