[thelist] SQL Madness! Many-to-many selection
rudy
r937 at interlog.com
Fri Aug 17 16:33:58 CDT 2001
> The relationship between event and category is many to many
hi joshua
arent those many-to-many relationships the most fun?
(stop that snickering in the back there)
>SELECT url_path
>FROM category
>WHERE id = (SELECT Min(category_id)
> FROM event_category_assoc
> WHERE event_id = event.id)
>
>Yuck Yuck Yuck... there has got to be an easier way!
there is, and you've got it right there in the subselect
(assuming you really do want the lowest category for each event)
just add the subselect to the WHERE clause of the three-way join
SELECT category.url_path AS category_url_path
, category.name AS category_name
, event.id
, event.name
, event.introduction
, event.is_active
, event.date_begin
, event.date_end
, DatePart(month, event.date_begin) AS month
, DatePart(year, event.date_begin) AS year
FROM event, event_category_assoc, category
WHERE event.id = event_category_assoc.category_id
AND event_category_assoc.event_id = category.id
AND event.date_begin >= GetDate()
AND category.id =
( SELECT Min(category_id)
FROM event_category_assoc
WHERE event_id = event.id )
rudy
More information about the thelist
mailing list