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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists