[thelist] SQL View: Join? SubQuery?

[rudy]

> Doesn't using %Like% take a really long time?

yes, but that goes away if PerformedPieces is a separate table

when you bring up a web page for a particular program, and you want to
include the performed pieces as well, you need a three-way join

select P.ID, Venue, PDate
       , CompositionTitle, ComposerName
     from Programs P
  inner
     join PerformedPieces PP
       on P.ID = PP.ProgramID
  inner
     join Compositions C
       on PP.CompID = C.ID
 where P.ID = 15

with the value 15 being supplied by your calling program

the above can be made into a view, if necessary

perhaps i should emphasize that a view is only a "redefinition" of a query

if you are running your queries from php or asp or coldfusion, there's
really no need to hide the three-way join from the calling program, and in
fact there's a good argument for leaving it in based on clarity and
understanding

rudy