I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...
lmao, never been accused of that before, my programming skills (and age) are much beyond student level but my SQL leaves alot to be desired. The tables have been simplified and context changed to protect the nature of the site so I'm sorry if it appears too simple.
One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! you have a match. It didn't work out so well and didn't get me any further to sussing out the 2nd piece of logic.
I've never really used the EXISTS clause (yes I know, blasphemy!) but will check it out later. Thanks for the pointer
I think I may of had a case of things-always-seem-much-simpler-when-explaining-to-someone-else syndrome :s The query I was using has been left at home so I decided to quickly knock one up as example and it seems to work now.
WHERE (PartId = 1 OR PartId = 3)
AND (SELECT COUNT(*) FROM tblModelPart AS tblModelPart2 WHERE tblModelPart2.ModelId = tblModelPart.ModelId) = 2
GROUP BY ModelId
HAVING COUNT(ModelId) = 2
Include the line in red for logic #1 and exclude it for #2. Is it really that simple or am I missing something completely obvious? I'd still be interested in seeing if there's a way using EXISTS.