Depending on the app, if this is a key feature, it'd be worth caching (either via the provided attributes in the <cfquery> tag or by storing the results in a scope (application or session). Doing it as one query just to save calls to the DB is rediculous. That's like saying to put all of your business logic, display code, etc in one file because then you know where everything is. It may make it easier to have everything in one place, but that doesn't make it right. Unless the data is truly related and requires a join, I would do it as 2 separate queries. Think of all of the overhead in this example that would come with all of the authors that don't have an article in the record set. You'd still have blank columns for all of the article fields.
John Burns -----Original Message----- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 2:08 PM To: CF-Talk Subject: Re: When two tables go to war (JOIN) I'm sorry I have to disagree... I'd much rather make one call to the DB and get all of my results in 1 query. It's faster. On Tue, 9 Nov 2004 19:59:14 +0100, Pascal Peters <[EMAIL PROTECTED]> wrote: > Don't join them, there is no reason to in this case. Use 2 queries. > > Pascal > > > > > -----Original Message----- > > From: Stuart Kidd [mailto:[EMAIL PROTECTED] > > Sent: 09 November 2004 19:22 > > To: CF-Talk > > Subject: When two tables go to war (JOIN) > > > > Hi guys, > > > > I've got two tables: > > > > 1 is full of articles (articleID, articleName, articleBody, > > authorID) > > 2 is full of authors (authorID, authorFirstName, authorSurname) > > > > I would like to join them. > > > > <cfquery name="showArticle" datasource="user020"> SELECT * FROM > > Articles WHERE articleID = '#url.updateID# </cfquery> > > > > And > > > > <cfquery name="getAuthor" datasource="user020"> SELECT authorID, > > authorFirstName, authorSurname FROM Authors </cfquery> > > > > Could somebody please explain how to join them so I only retrieve > certain > > articles (where articleID = #url.updateID#) but get all the Authors > (as I > > want to populate a list whereby the user can select who is the > > author > of > > this particular article). > > > > Thanks very much guys, > > > > Saturday > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183786 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
