[thelist] SQL / Tables (CF)

[Drew Shiel]

Hi folks;

I have a problem here that is doing my head in. This isn't a fully-in-use 
production system yet, so I can make changes to one of the tables (Rss) if 
my problem can't be solved via SQL or CF scripting.

It's a tough one to even explain, and it's taken me most of the afternoon 
to explain to myself, so comprehension hats on, please. :)

I have two tables.

"Rss" contains the fields "Name", "EventTypeIDs", and a number of others, 
which aren't awfully relevant, one of which is "Description". "Name" is 
unique, "EventTypeIDS" is a string, which may be a single number, like "12" 
or "42", or a comma separated list, like "24, 26, 45"

"EventType" contains the fields "EventTypeID", which is a unique numeric 
string, and "CategoryID", which is a numeric string, and a few others, 
which are not important. All the IDs in Rss.EventTypeIDs are assigned a 
CategoryID in EventType. Several IDs can have the same CategoryID

The EventTypeIDs in a given Rss record do not all necessarily correspond to 
one single CategoryID, although they may.

What I want to have at the end of this query, or script, or whatever, is:

EventType.EventCategoryID, Rss.Name, Rss.Description (etc) for each Rss 
record AND another row for each matching EventCategoryID not already shown.

That is: For each CategoryID which has any corresponding RSS feeds, present 
all the corresponding RSS feeds.

Example:

Rss:

Name | EventTypeIDs | Description
soc | 24,34,45 | Soccer
45 | 45 | EUFA
24 | 24 | World Cup
amer | 1,4,10 | American Sports
1 | 1 | NFL
4 | 4 | NBA
72 | 72 | Kentucky Derby

EventType:

EventTypeID | EventCategoryID
1 | 1
4 | 2
10 | 3
24 | 4
34 | 4
45 | 4
72 | 11

And what I want back is:

1 | amer | American Sports
1 | 1 | NFL
2 | amer |  American Sports
2 | 4 | NBA
3 | amer |  American Sports
4 | soc | Soccer
4 | 24 | World Cup
4 | 45 | EUFA
11 | 72 | Kentucky Derby

Clear as mud? Help!

Drew.

-------------- next part --------------

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.719 / Virus Database: 475 - Release Date: 12/07/2004