[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