[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