[thelist] DB design question

[Joel Canfield]

--
[ Picked text/plain from multipart/alternative ]
> 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,

Well, blow me down; I didn't know that

 and my MySQL book says
> they're equivalent.
>     I'll bet that's what killed it.

Yup.

> - parenthesis added around first table/join.
> - removed "as a" - assumed this was the "pseudo-like" part.
> - did not ORDER BY anything

Off the top of my fuzzy-just-woke-up-from-a-nap head, if you switch left
joins for right joins, you have to swap the order of the tables in each join
(left join = grab all from the table on the left, and all matching from the
table on the right)

One purpose of declaring a left or right join is to tell the query which
table you consider the 'master' of that bit of data; so, I figured the
amenities table would have more amenities than the others, and that's where
I started. You'll just have to reverse.

However, if you take rudy's subsequent advice to just add boolean fields for
the amenities to the Properties table, joins go away and simplify the query
immensely.

joel