The older Transact SQL syntax (*=) is pretty widely supported, but as Adam suggested for portability (and IMO readability) it is better to use the ANSI SQL syntax (LEFT OUTER JOIN).
In addition, both Microsoft and Sybase reccommend using ANSI style joins. http://www.microsoft.com/sql/using/tips/development/July23.asp </rob> -----Original Message----- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 2:35 PM To: CF-Talk Subject: Re: SQL Question Adam, Is the *= universal syntax (MS SQL, Access, Oracle, MySQL ect..?) Thanks that is working beautifully! Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector Commerce Builder > SELECT h.id, h.header, l.link, l.link_title > FROM headers h, links l > WHERE h.id *= l.headerid > > //notice the star on the left side of the = in the condition > > > Adam. > > > > -----Original Message----- > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, May 09, 2002 1:04 PM > > To: CF-Talk > > Subject: OT:SQL Question > > > > > > I have two tables I am trying to join, one is a header and > > one is the links > > under that header. > > > > header table: > > id, header > > > > links table > > id, headerid, link, link_title > > > > I am writing a join query to pull up all the headers and the > > links that go > > with a particular header, but I if there are no links > > associated with the > > header, it won't display anything. I want it to show the > > headers with 0 > > links or with 100 links associated. > > > > > > Query is: > > > > <cfquery datasource="#prefs.dsn#" name="header"> > > select h.id, h.header, l.link, l.link_title > > from headers h, links l > > where f.sub_object = l.headerid > > and f.sub_object = h.id > > group by h.header, f.state, f.sort, l.link, l.link_title, h.id > > </cfquery> > > > > Any help would be appreciated. > > > > Paul Giesenhagen > > QuillDesign > > http://www.quilldesign.com > > SiteDirector Commerce Builder > > > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
