[thelist] DB design question
Ed McCarroll
Ed at ComSimplicity.com
Fri Dec 6 20:21:00 CST 2002
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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
More information about the thelist
mailing list