I don't want to stretch this thread to much, but an inner join won't do at all. He needs the info on ONE specific article (with an author) and the info on ALL the authors (probably to populate a select in a form). An inner join will only return the info on the author related to the article.
As I said before: for an edit form, I will make one query for the object's (read article's) data and then a query for each foreign key so I can have a select to enter the FK. I don't see the need to get all that in one query. If you would have more than one foreign key, getting it all in one query would make the output nearly impossible. Usually the data in the queries for the lookup tables (FK) doesn't change much, so it can easily be cached. Pascal > -----Original Message----- > From: Robertson-Ravo, Neil (RX) [mailto:Neil.Robertson- > [EMAIL PROTECTED] > Sent: 10 November 2004 09:22 > To: CF-Talk > Subject: RE: When two tables go to war (JOIN) > > 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:183835 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
