[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