Neil, It's not that he wants to get the author of the given article - he wants to get a list of all authors so that his app can assign one to the article, so an INNER JOIN is kind of out of the question.
Outer joining would pull back the full text (and whatever other fields are selected) once for each of the authors in the author table. This could get very bandwidth-intensive! I'd suggest one of the following: 1. Continue to use two seperate queries (one for the article, one for the set of authors). Use ColdFusion's built-in caching (cachedwithin or cachedafter) on the author query to reduce overhead. -or- 2. If you're really worried about multiple database connections, create a stored procedure that takes an articleId and returns two recordsets - one for the article, one of all authors. -joe On Wed, 10 Nov 2004 08:22:24 -0000, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > An INNER JOIN join is the best option by far. Use one query to build one > recordset object and reference that. There is no real reason for using two > queries here. > > > > > -----Original Message----- > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > Sent: 09 November 2004 19:14 > To: CF-Talk > Subject: Re: When two tables go to war (JOIN) > > Think about how much more crap you gotta send back to the app server > if you do it in one query. Not to mention the fact that you're > requestion to totally different types of data that have exactly zero > to do with eachother. > > Say you've got 50 authors. If you do a join, you're going to be > pulling the full text of the article back across the network 50 times, > rather than once if you do it with separate queries. > > No ifs, ands, or buts, two separate queries is the way to go. > > cheers, > barneyb > > On Tue, 9 Nov 2004 13:08:05 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote: > > 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. > > > -- > Barney Boisvert > [EMAIL PROTECTED] > 360.319.6145 > http://www.barneyb.com/blog/ > > I currently have 0 GMail invites for the taking > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:183843 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
