[thelist] DB design question

[Ed McCarroll]

Joel, Rudy, anyone?

I don't know if you care to pursue this, but if you are ...  It's become
a bit of a challenge to me.  (Not an issue involving anything I'm in
trouble if I don't solve.)  This kind of issue has kept me from using
many-to-many relationships, so I'd love to crack it.

I read Rudy's article and gathered that it would work to build a
sub-select for each amenity upon which I want to search.  To search for
a listing with 4 bedrooms, 2 baths, 3 fireplaces, 1 heliport, and 17
doghouses, I would need 5 sub-selects.  Right, Rudy?

 (Article:

http://searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid497037_tax285649,00.html

          )

Using what I hope equates to Joel's suggested query, I attempted to select
all listings with both bedrooms and bathrooms, but ended up selecting any
listing with bedrooms.

I put a screenshot (74KB) of my data and results up at:

  http://www.comsimplicity.com/screenshots/Amenities.jpg

The query I used was:

SELECT DISTINCT Properties.PropertyID, Properties.address
FROM (Amenities RIGHT JOIN PropertyAmenity
ON Amenities.AmenityID = PropertyAmenity.AmenityID)
RIGHT JOIN Properties
ON PropertyAmenity.PropertyID = Properties.PropertyID
WHERE Amenities.AmenityID
IN (1,2);


I made the following changes to Joel's "less pseudo-like code":
- I dropped all aliases, used table names throughout
- I changed LEFT to RIGHT join
    Access only supports right, and my MySQL book says they're equivalent.
    I'll bet that's what killed it.
- parenthesis added around first table/join.
- removed "as a" - assumed this was the "pseudo-like" part.
- did not ORDER BY anything

Joel's original pseudo-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

Anyhow, TIA for any lessons you care to teach me.  And I guess I
owe a tip.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Ed McCarroll                          MailTo:Ed at ComSimplicity.com
ComSimplicity                                      (310) 838-4330
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -