[thelist] SQL View: Join? SubQuery?

rudy r937 at interlog.com
Tue Aug 20 12:44:16 CDT 2002


hi susan

it requires an unconventional join

  select ProgramID, Venue, VDate, Conductor
       , CompositionTitle, ComposerName
     from Programs
  inner
     join Compositions
       on CompID in ( PerformedPieces )

note i added a PK for Programs to make some logic easier (see below) and
changed the name to VDate (Date is a reserved word)

please let me know if that does not work

you will have to do some "curr/prev" logic in your app in order to list the
individual compositions for each program without repeating the Venue,
VDate, and Conductor

or in coldfusion, just

  <cfoutput query=queryname group=ProgramID>
     Venue: #Venue#
     Date: #VDate#
    Conductor: #Conductor#
    Performed Pieces:
      <cfoutput>
        #CompositionTitle# - #ComposerName#
      </cfoutput>
  </cfoutput>

notice how the curr/prev grouping logic is completely built in -- no
indexes, no cursors, no IF test, no fuss, no muss

of course, it's messier if the Programs table is missing a PK......   ;o)




More information about the thelist mailing list