paul,
try this:
<cfquery datasource="#prefs.dsn#" name="header">
select h.id, h.header, f.state, f.sort, l.link,
l.link_title
from headers h left join fusion f on h.id=f.sub_object
left join link l on f.sub_object = l.headerid
where f.owner = '#prefs.indexid#'
group by h.header, f.state, f.sort, l.link, l.link_title,
h.id
</cfquery>
~ dina
----- Original Message -----
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, May 09, 2002 2:15 PM
Subject: Re: SQL Question
> 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
> > > >
> > > >
> > >
> >
> >
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.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
