I don't know why I have so much trouble with these...
OK, here's the Setup
I have 4 Tables, with a bunch of stuff to look up in all of them and
then return results. So, simplified:
1. Shows
ShowID
Title
Network - searchable
Desc - searchable
2. SubjectMatter
ShowID
SubjectType - searchable
3. Credits
ShowID
PlayerID
Credit Type - searchable
4. Players
PlayerID
FName
LName
Results I'm looking for is FName LName the Players Table
So, if I search for:
DIRECTORS (from Credits)
SciFi (Network from Shows)
Blood (Desc from Shows)
True Crime (SubjectType from SubjectMatter)
Results format is
<cfoutput>
Players.FName Players.LName
<loop>
Shows.Title - Network
</loop>
</cfoutput>
Any of the search fields can be left BLANK as well, so you could search
only for shows with "Blood" in the description and "True Crime" as the
subject matter - and it would return directors of matching movies from
all networks, for example
I can do this if I make about a dozen smaller queries, which is
obviously a waste of resources. As soon as I try to make a decent
compound query I get buggered though.
So, something like:
SELECT
Players.FName,
Players.LName,
Shows.Title,
Shows.Network,
Credits.CreditType
FROM
Players,
Credits,
Shows
INNER JOIN Credits c ON (Shows.ShowID = c.ShowID)
INNER JOIN Players p ON (Credits.PlayerID = p.PlayerID),
SubjectMatter
INNER JOIN Credits d ON (SubjectMatter.ShowID = d.ShowID)
INNER JOIN Players l ON (Credits.PlayerID = l.PlayerID)
WHERE
(Shows.Network = '#form.network#')
AND (Credits.CreditType = '#form.credittype#')
AND (Shows.Logline like '%#form.logline#%')
ORDER BY
Players.LName
Don't look too bad, but it just times out. I've tried a dozen variations
of this and nothing works so far.
I'm using mySQL.
Ok, pointers and a link to the definitive SQL Inner Join Tutorial might
be in order now.
Thanks,
--
-----------
Les Mizzell
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222543
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
