Optimizing query with having and count
Can anybody help me with optimizing this Query?
I want to get all the item_id's with a variable nr of linked options.
The main table is items, Then there is a second table with filteroptions. The filteroptions are linked to items in a many to many relation by the table filteroptionslinks.
Table structure:
----items----
item_id
....
--filteroptionslinks--
option_id
item_id
--filteroptions--
option_id
...
SELECT fol.item_id
FROM filteroptionslinks fol
INNER JOIN items ON fol.item_id = items.item_id
WHERE items.publish = 'Y'
AND fol.option_id = 112
AND fol.option_id = 115
GROUP BY fol.ITEM_ID
HAVING count(fol.item_id) = 2
Then i need an addition to count the nr of results of this query. Currently i can't do this by saying SELECT COUNT(fol.item_id) because this will just return multiple rows with the value 1.
Subject
Views
Written By
Posted
Optimizing query with having and count
2600
January 13, 2011 05:58AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.