There has to be a better way!?
Posted on May 13, 2005
Sorry for the lack of posts recently. I am working on a number of items which I hope to post more about soon (g11n in particular)...but for the moment I want to share an interesting problem. I think maybe I am being blind here and ended up making this query more complex than it has to be (and I am hoping someone can straighten me out - or maybe tell me this is correct).
The problem is with a query that I am rebuilding (the original was not mine). For arguments sake let's say this, the pages that call this query send a list of brands for a product. Brands and products have a many to many relationship, and what we need to know is which products are in all of the brands on the list.he original query (which was inside an excrutiatingly slow stored procedure that I have simplified for the sake of discussion) used the following code:
FROM products p
WHERE EXISTS(
SELECT 1
FROM product_brands pb
WHERE pa.brandID IN (<cfqueryparam value="#arguments.listBrandID#" cfsqltype="cf_sql_integer" list="true">)
AND pb.productID=p.productID)
This doesn't work for the fairly obvious reason that the IN (list) functions like an OR. So, instead of getting products in all categories, we received products in any of the categories. I debated if it could be done using an outer join, but each record returned in the resultset would only match one of the items in the list.
Finally I arrived at this solution:
FROM products p
AND p.productID IN (
SELECT productID
FROM product_brands
WHERE brandID IN (<cfqueryparam value="#arguments.listBrandID#" cfsqltype="cf_sql_integer" list="true">)
GROUP BY productID
HAVING count(productID) = <cfqueryparam value="#listLen(arguments.listBrandID)#" cfsqltype="cf_sql_integer">)
The logic is that I check a count of grouped product IDs in the subquery against the length of the list, and only send back those that matched all. It appears to work fine and run relatively fast...my only problem is that it seems like a complex solution to a simple problem. I am sure I am missing a more straightforward solution, any ideas?
P.S. Thanks to Shannon Hicks for letting me bounce my idea off of him and helping refine the code
Comments
There are currently no comments for this entry...be the first!