glad that helped, paul. here's a link you may want to check out. it's a good sql tutorial:
http://www.devguru.com/features/tutorials/AdvancedSQL/advancedSQL html good luck, ~ dina ----- Original Message ----- From: "Paul Giesenhagen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, May 09, 2002 2:45 PM Subject: Re: SQL Question > Bingo ....!!! I truely appreciate the help. > > I am back in the status of rookie. We have changed our method of database > design and thus getting into all kinds of joins .... so I guess it is off to > the books to do some basics! > > Thanks again! > > Paul Giesenhagen > QuillDesign > http://www.quilldesign.com > SiteDirector Commerce Builder > > > > > > 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
