[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

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

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


More information about the thelist mailing list