One technique to use for complex joins is to build the joins in
views. (I rarely use a join in a cfquery) By using views, the
execution plan for the join is stored with the view and does not have
to calculated with the dynamic SQL in a cfquery. By using views for
joins, it allows focusing on the results of the joins before adding
other conditions.
For example:
CREATE VIEW dbo.vShowDetail
AS
SELECT P.FNname, P.LName, C.CreditType, S.Title, S.Network,
S.[Desc], SM.SubjectType, S.Logline
FROM dbo.tCredits C INNER JOIN
dbo.tPlayers P ON C.PlayerID = P.PlayerID INNER JOIN
dbo.tShows S ON C.ShowID = S.ShowID INNER JOIN
dbo.tSubjectMatter SM ON S.ShowID = SM.ShowID
ORDER BY P.LName
Then the cfquery is simpler
SELECT TOP 100 PERCENT FNname, LName, CreditType, Title, Network, Logline
FROM dbo.vShowDetail
WHERE (Network = 'A') AND (Title = 'B') AND (CreditType = 'C')
At 05:27 PM 10/28/2005, you wrote:
>Try the ColdFusion MX Bible it has some very good chapters SQL
>
> >> Is there a damned good book/tutorial somewhere on writing these things?
> >
> >"Using Complex SQL Statements in Coldfusion for Idiots" ??
> >
> >--
> >-----------
> >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:222646
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
