That being the case, what about this query?
<cfquery datasource="#prefs.dsn#" name="header">
select h.id, h.header, f.state, f.sort, l.link, l.link_title
from headers h, fusion f, link l
f.sub_object = l.headerid
where f.owner = '#prefs.indexid#'
and f.sub_object = h.id
group by h.header, f.state, f.sort, l.link, l.link_title, h.id
</cfquery>
There are more then two tables involved here.. You can't outer join on
joined tables...
> 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
