Multiple WHERE replacement??

Hello,
I have a table relationship that connects a list of Amenities provided by
each Property, such as:
Properties
--
PropertyID int
PropertyName nvarchar(100)
Amenities
--
AmenityID int
AmenityName nvarchar(50)
PropertyAmenities
--
PropertyID int,
AmenityID int
I want to be able to pull a list of Properties that have a certiain
Amenities, such as this static query:
SELECT PropertyID, PropertyName
FROM Properties p
WHERE 1 = 1
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 1)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 2)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 3)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 4)
QUESTION:
What is the best way to setup an efficient Stored Procedure to run this
query, based on a dynamic list of AmenityID's? I'm thinking that there's a
better way, than to dynamically build these WHERE clauses and pass them to
the SQL Server.
I might pass seven integer values on one execution, and three on another.
Thank you for any light you can shed on this.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Vito D" wrote:

> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>
>|||Since you didnt post DDL, I didn't build the tables and test this, but give
it a shot.
This is basically relational division, which you can find better examples of
if you search for posts by CELKO in this news group, or just do a google
search. I have nto really worked with this much, but I think this should
work.
Combine this with dynamic SQL to build your in clause (or populate a temp
table with the values for your in clause).
This code assumes that you have PKs on properties(PropertyID),
PropertyAmenities (PropertyID, AmenityID) and Amenities(AmenityID).
SELECT prop.PropertyID, prop.PropertyName, count(1)
FROM Properties prop
inner join PropertyAmenities propamen
on propamen.PropertyID = prop.PropertyID
where propamen.AmenityID in (1,2,3,4)
having count(1) = (select count(1)
from Amenities amen
where amen.AmenityID in (1,2,3,4)
)
"Vito D" <VdeleteDeCarlo@.ThePulse.remove.com> wrote in message
news:uVEt8btbGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>