[thelist] SQL View: Join? SubQuery?
Sean G.
ethanol at mathlab.sunysb.edu
Tue Aug 20 17:16:05 CDT 2002
Howdy,
A little rearrangement of your tables will make your querying much easier.
Fields of lists may seem convenient at first but will end causing a lot of
problems and ugly code. Right now you want a list of compositions for each
program, but what happens when you want a list of programs featuring a
particular composition? Plus, WHERE X in (list) is a lot more work for your
server than WHERE X = Y.
So try something like;
Compositions
ID
ComposerName
Title
Programs
ID
Venue
Date
Conductor
PerformedPieces
ID
ProgramID
CompositionID
And then you query for details of the compositions for a particular program,
but just as easily turn around and find details on programs featuring a
particular composition or composer.
HTH,
Sean G.
>-----Original Message-----
>Greetings!
>
>I am trying to put together a View in MS-SQL. At this point, I am just
>looking for a push in the right direction so that I can use trial and error
>to learn this syntax, because it will come up again. :)
>
>Here are the Tables I have:
>
>Compositions
> CompID
> ComposerName
> CompositionTitle
>
>Programs
> Venue
> Date
> Conductor
> PerformedPieces
>
>The field Performed Pieces in Programs is a list (1, 287, 543, 2)
>where each list item would match the CompID from Composers, etc
>
>The end result needs to be a completed Program Record, where all fields are
>shown, including the Title and Composer Name from Compositions. For example:
>
>Venue: Jones Hall, Houston Texas
>Date: August 20, 2002
>Conductor: Elmer Fudd
>Performed Pieces:
>Composition in A Minor (for Marimba) - Bugs Bunny
>Corino Falso - Eliades Ochoa
>Camp Hill Comrade - Harland Mitchell
>
More information about the thelist
mailing list