[thelist] basic SQL many-many optimization
rudy
rudy937 at rogers.com
Wed Jul 16 17:20:41 CDT 2003
>> Title Genres
>> T1 g1, g2, g3
>> T2 g1, g2
>> T3 g1, g2, g3, g4, g5
>
> Unfortunately, I've never done something like this.
> I'm hoping someone like Rudy has and can paste some old code
that's not a recursive thing at all, that's a many-to-one denormalization
;o)
no way do you want to even attempt this in sql
what you want to do is return your result set, yes, with the same title on
multiple rows, then print one line for each title, and "collapse" the
various genres onto that line
piece-of-cake do-it-in-yer-sleep one-hand-tied-behind-your-back if you have
coldfusion (using the CFOUTPUT GROUP= parameter)
in other scripting languages, you gotta do the "current/previous" logic
yourself
here's the pseudo-code:
set previoustitle = '' /* empty */
while not eof
get next record
if currenttitle > previoustitle
print title on new line
set previoustitle = currenttitle
print genre on same line
rudy
More information about the thelist
mailing list