[thelist] DB design question

[Joel Canfield]

--
[ Picked text/plain from multipart/alternative ]
> The thing I don't see how to address in a many-to-many
> approach is how you would search for multiple criteria.  What
> kind of JOIN are you going to use that will allow you to
> SELECT rows that match values in two different amenities,
> e.g. bedrooms=3 and baths=2?  If you can do that efficiently,
> could you please educate me; I sure could use it.

pseudo code/explanation:

select distinct properties with these particular amenities from Properties
and Amenities as they're joined by the PropertyAmenities table

Less pseudo-like code:

Select distinct Properties.PropertyID (plus whatever other fields you want)
from Amenities as a
left join
PropertyAmenities as pa
on
a.PropertyID = pa.PropertyID
left join
Properties as p
on
pa.PropertyID = p.PropertyID
where
pa.AmenityID in
(
1
, 2
, 6
, 9
)
order by
whatever field you want


Goes to the list of amenities and finds the ones you're interested in;
references the lookup table to, well, look up the IDs of the properties
having those amenities, and then gets the property info from the Properties
table.

I won't swear my sequence of left joins is correct; I'd have to play with it
a bit to be sure.

joel