[thelist] SQL Madness! Many-to-many selection
Joshua Olson
joshua at alphashop.net
Fri Aug 17 15:51:38 CDT 2001
----- 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
More information about the thelist
mailing list