[thelist] SQL Madness! Many-to-many selection

[Joshua Olson]

----- Original Message -----
From: "Anthony Baratta" <Anthony at Baratta.com>
Subject: Re: [thelist] SQL Madness! Many-to-many selection


: At 01:37 PM 8/17/2001, you wrote:
: >I have events in one table, and categories in another.
: >
: >Each category has a distinct url and a name.
: >
: >What I am trying to do is select the latest events and grab the
information
: >for exactly one category in which it exists, without selecting the same
: >event more than once.
:
: There might be a better way, but this is what I would try.
:
: Select Distinct URL from Table_One, Table_Two
: Where Table_One.URL_ID = Table_Two.URL_ID
: AND Table_Two.Category in ('Cat_1','Cat_2','Cat_3')

Thanks for the quick response, but unfortunately it's not that easy :(

The relationship between event and category is many to many, so the query
looks more like this:

  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_category_assoc.category_id = category.id
    AND event_category_assoc.event_id = event.id
    AND event.date_begin >= GetDate()
  ORDER BY date_begin ASC

I could make the following changes:

1. Remove category from the FROM clause
2. Replace the "category.url_path AS category_url_path" with a subselect
that SELECTS the category url path based on the category from the lowest
category_assoc id that exists.
3. Use the same logic to select the name of the category

That subquery would look something like:

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!

Thanks again,

-joshua