Query Logic - SQL Help

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 ...

tblModel tblPart
ModelId ModelName PartId PartName
------- -------
1 Alpha 1 CHOO1 Stem
2 Bravo 2 BH034 Rod
3 Bravo Pro 3 HRE Seat

tblModelPart
ModelPartId ModelId PartId
----------
1 1 1
2 2 1
3 2 3
4 3 1
5 3 2
6 3 3

... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...

1. Return all models that contain only the parts selected.

ModelId ModelName
-------
2 Bravo

2. Return all models that contain the parts selected, and may contain other parts.

ModelId ModelName
-------
2 Bravo
3 Bravo Pro

... so do you have any idea on how the SQL would look for either of these queries?

Thanks in advance,
GoranWhat have you tried so far?

This sounds a lot like homework to me, and at DBForums we don't do homework outright (although we'll often provide guidance).

For what it is worth, I'd probably read up a bit on the EXISTS clause if I were you.

-PatP|||HOMEWORK!!!

yes, i have a very clear idea what the SQL would like like

you must give it a try yourself, nobody here does homework assignments

however, we will help you with it, once you put in the necessary effort

:)|||rats, sniped again :(|||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 ;)|||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! this is the solution i would have offered, it works ~so~ well

could you show your query please?|||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.

SELECT ModelId
FROM tblModelPart
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.|||since we are looking for presence or absence of more than one row, i.e. a group, therefore GROUP BY is necessary
SELECT ModelId
FROM tblModelPart
GROUP
BY ModelId
HAVING ...this is the basic approach, which you anticipated

now for specific situations, simply count what you want

for #1, use
HAVING SUM(CASE WHEN PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 2
AND SUM(CASE WHEN NOT PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 0
for #2, remove the AND condition|||actually, #1 can be simplified (and made more efficient) like this:SELECT ModelId
FROM tblModelPart
WHERE PartId IN ( 1,3 )
GROUP
BY ModelId
HAVING COUNT(*) = 2this means fewer rows go into the grouping process