Evil SQL Statment

Ok heres the scenario:
2 Tables
Properties
PropID - Int - Identity
Class - Int
Address - varchar
Etc..
Property_Classes
ID - Int - Identity
PropID - Int
Class - Int
The properties table has one entry per property. The Property classes
table can have multiple entries for a Property, or no entry at all.
I need a way to find all distinct properties with 1 or more specific
classes. For example I need to find all properties with class 5 and
class 20. The property must have both classes but the class can be in
the class field on the properties table and/or the class field in the
property_classes table. So one might be in properties and the other in
property_classes or both in property_classes.
I might be looking for properties with up to 5 classes... Does any one
have a suggestion on how to accomplish this in a SQL Statement so I
don't have to open a bunch of records in a vb app and search through
them to find the property?
Thanks a lot!(not tested)
DECLARE @.Classes TABLE (Class INT)
INSERT INTO @.Classes (Class) VALUES (5)
INSERT INTO @.Classes (Class) VALUES (20)
SELECT PropID
FROM (SELECT PropID, Class FROM Properties
UNION SELECT PropID, Class FROM Property_Classes) p
INNER JOIN @.Classes c
ON p.Class = c.Class
GROUP BY p.PropID
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.Classes)
Jacco Schalkwijk
SQL Server MVP
<nsmith@.mysteryshops.com> wrote in message
news:1122481428.120240.189510@.g44g2000cwa.googlegroups.com...
> Ok heres the scenario:
> 2 Tables
> Properties
> PropID - Int - Identity
> Class - Int
> Address - varchar
> Etc..
>
> Property_Classes
> ID - Int - Identity
> PropID - Int
> Class - Int
>
> The properties table has one entry per property. The Property classes
> table can have multiple entries for a Property, or no entry at all.
> I need a way to find all distinct properties with 1 or more specific
> classes. For example I need to find all properties with class 5 and
> class 20. The property must have both classes but the class can be in
> the class field on the properties table and/or the class field in the
> property_classes table. So one might be in properties and the other in
> property_classes or both in property_classes.
> I might be looking for properties with up to 5 classes... Does any one
> have a suggestion on how to accomplish this in a SQL Statement so I
> don't have to open a bunch of records in a vb app and search through
> them to find the property?
> Thanks a lot!
>|||That works perfectly. Thank you for the suggestion!