next »

[subclass]

(think I need some fresh eyes - long day)

Am after the intersect of two queries, not sure of the most efficient query to do it.

Table:-

`product_filters`

id - product_id - filter_id
------------------------
1        3                7
2        3                9
3        3               12
4        4                9
5        4               12

basically want to get a list `product_id`s from product_filters where filter_id = 9 and 12

[nutballs]

i think you need to do sub-selects

SELECT product_id FROM table
WHERE product_id IN (SELECT product_id FROM table WHERE filter_id=9)
AND product_id IN (SELECT product_id FROM table WHERE filter_id=12)

of course if that is a really big table that query is going to suck a bit potentially.

im trying to think if there is another way.

[subclass]

Maybe I've structured the table wrong - basically a system which breaks down products by a clickable filter/tag list 'red','green','big','small' etc and displays the resulting products [with the option to refine further]

Once I crack it will post up here for ppl to take a look at it.

btw am running my e-commerce sites now using nested set model, anyone tried it before? really enjoying working with it

[edit] that's a go - that query did work NB thanks! I put a wrong digit somewhere [/edit]

[nutballs]

the only problem is going to be if you have a large table and are going to have a potentially large number of criteria. 5 ANDs on a big ass table and you might start hating things.

another way is a bit of a back door approach, but might be a bit easier to wrangle, and might even be more efficient:
SELECT product_id
FROM table
WHERE filter_id=9 OR filter_id=12
GROUP BY product_id
HAVING count(product_id)=2

the having clause is equal to the number of filters you are setting.

[subclass]

the only problem is going to be if you have a large table and are going to have a potentially large number of criteria. 5 ANDs on a big ass table and you might start hating things.

another way is a bit of a back door approach, but might be a bit easier to wrangle, and might even be more efficient:
SELECT product_id
FROM table
WHERE filter_id=9 OR filter_id=12
GROUP BY product_id
HAVING count(product_id)=2

the having clause is equal to the number of filters you are setting.

This is working fantastically well

[vsloathe]

Be careful with nested subqueries. MySQL is a real plodder when it comes to those.

[nutballs]

yea, thats why i thought about the count method and posted that. the subqueyr method will be run as 1 query plus how ever many filter queries you have, in this case 2, so 3 total qeuries, and in this case 3 full scans of the table.

the count method I think only does 1 full scan, and then refines down from their to your results.