With SQL Server, you would do a LEFT JOIN:
<cfquery datasource="#prefs.dsn#" name="header">
select h.id, h.header, l.link, l.link_title
from headers h LEFT JOIN links l ON f.sub_object = l.headerid
where f.sub_object = h.id
group by h.header, f.state, f.sort, l.link, l.link_title, h.id
</cfquery>
Which DB are you using?
--
Scott Van Vliet
Sempra Energy
555 W. 5th St., 21st Floor
Los Angeles, CA 90013
Tel > 213.244.5205
Email > [EMAIL PROTECTED]
> -----Original Message-----
> From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 11:04 AM
> 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
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
