[thelist] SQL View: Join? SubQuery?

Susan Wallace susanhw at webcastle.com
Wed Aug 21 11:10:01 CDT 2002


Ok, now my head is going to explode. ;)

I have been working to sort through how I would bring this all back
together using Sean's example, and I still see that I would at some point
have to parse through a list.

Using Sean's suggestion, and adding a little more detail to what I am
trying to accomplish here, this is where I am stuck:

I have 3 separate databases that are not *actually* related yet, but I am
trying to establish a linking entity to put all of this together. Sean
addressed a real case that will come up.

Initially I had the following (abbreviated)

Siwe
         ID
         Composition Title
         Composer
         Composition Details
         Composer Details

In addition to the Siwe Database, I also had

Reviews
         ID
         Composition Title
         Composer
         Review

AND Now:

Programs:
         ID
         Venue
         PDate
         Performed Pieces

In all actuality, these three items are not related at all, even though
they do have overlapping information. (Yes, I know, it needs to be
restructured. This band-aid is not my first choice)

The end result is to have them *appear* to be related to the end user,
requiring that I create the Compositions Table I mentioned yesterday.

The Compositions Table looks like this:

Compositions:
         ID
         Title
         FirstName
         LastName
         ReviewNo
         SiweNo
         IsProgram (I forgot that I had decided to change this to y/n as in
"Has this been performed in Programs"

The idea is that if someone is searching through the Siwe stuff, and they
want to see if there is a Review of the Composition, or how many times it
was played over the last year in Programs, The "behind the scenes" part
would find the SiweID in Compositions, and then provide links directly to
the Review, and a link to a page that would locate the CompID in Programs,
and show them the programs.

Make sense?

Breaking this back down into Programs, using Seans Example, I would have this:

Compositions:
         CompID
         Title
         FirstName
         LastName
         ReviewNo
         SiweNo
         IsProgram

Programs:
         ProgID
         Venue
         PDate
         Details


PerformedPieces
         ID
         ProgID
         CompID

So, if I bring up a program based on a state, it would show the details,
then have to query PerformedPieces where ProgID = ##

 From there, I have to get the CompID's from that result, then find the
Title and Composer in Compositions where CompID IN ##, which is a list

Hmm... that confused me:

ProgramA (ID=15)
State
Venue
PerformedPieces:

SELECT *
 From PerformedPieces
WHERE ProgID = 15

Returns a List: (1, 56, 87, 30)

Then:
Select *
FROM Compositions
WHERE ID = (one of the list items)

I can run that through an index loop using CF on the page, but that seems
inefficient.

Can I combine programs back into one View and then have:

ProgramA
State
Venue
Pieces:
Title Composer
Title Composer
Title Composer

Etc?

Doesn't using %Like% take a really long time? (comparatively speaking...)

Thanks for making me think and helping prevent a disaster!

Susan

>Susan, Sean was absolutely right, you will save yourself many headaches
>down the road by changing the table structure
>
>your idea of using a view is excellent, but that's a separate question,
>although it will certainly be better to have a view based on a solid
>underlying structure
>
>the only comment i have about Sean's proposed relationship table --
>
>PerformedPieces
>ID
>ProgramID
>CompositionID
>
>is that you probably don't need the ID as the key to this table
>
>use the combined ProgramID and CompositionID as the primary key




More information about the thelist mailing list