I use where clause join syntax for more reasons that just portablity.
Besides the fact that the tables involved in the query are easier to
identify and my opinion that it's easier to read, I find it easier to
write dynamic sql using this syntax. For example:
<cfset thePeopleId="1111">
<cfif getdetail is 1>
<cfset SQLfields=", b.firstname, b.lastname">
<cfset SQLtable=", peopleDetail b">
<cfset SQLwhere="AND a.peopleid = b.peopleid">
<cfelse>
<cfset SQLfields="">
<cfset SQLtable="">
<cfset SQLwhere="">
</cfif>
<cfquery name="myquery" datasource="#dsn#">
SELECT a.idpeople#SQLfields#
FROM people a#SQLtable#
WHERE peopleid =#thePeopleId#
#SQLwhere#
</cfquery>
Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525
>>> [EMAIL PROTECTED] 05/10/02 02:38PM >>>
so are you saying that even though you use ansi joins, you should
probably be using *= for portability?
~ dina
----- Original Message -----
From: "Dave Watts" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, May 10, 2002 1:32 PM
Subject: RE: SQL Question
> > > Is the *= universal syntax (MS SQL, Access, Oracle, MySQL
> > > ect..?)
> >
> > it's my understanding that the *= syntax is legacy syntax; i
> > believe you'll want ansi syntax to effect compatibility with
a
> > most dbms's:
> >
> > SELECT h.id, h.header, l.link, l.link_title
> > FROM headers h LEFT JOIN links l ON h.id = l.headerid
>
> While you're correct to point out that the *= syntax is not
ANSI SQL, I
> suspect that it's supported by more databases than the ANSI
JOIN syntax! We
> typically use ANSI joins, but I remember an unpleasant
occurance porting
> something from MS T-SQL to Sybase T-SQL, and that version of
Sybase didn't
> support ANSI join syntax.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> voice: (202) 797-5496
> fax: (202) 797-5444
>
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
