[thelist] basic SQL many-many optimization

[rudy]

>> 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