[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