[thelist] SQL / Tables (CF)
Drew Shiel
ashiel at sportsinteraction.com
Fri Jul 16 10:23:51 CDT 2004
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
More information about the thelist
mailing list