Oops, I left for the day. Yes, you are correct. --Jeff
On 5/16/2005 5:30 PM, Tim Laureska wrote: > I checked that out & it said in the "nested loop/inner join" popup > window: For each row in the top (outer) input, scan the bottom > (inner)input, and output the matching rows. > > Just so I understand this correctly - what is being referenced as the > "top (outer)" input and the "bottom (inner) input" ?? > > I would assume outer means the "members m" table and inner means > "members_categories d" table ?? > > -----Original Message----- > From: Jeff Langevin [mailto:[EMAIL PROTECTED] > Sent: Monday, May 16, 2005 4:59 PM > To: CF-Talk > Subject: Re: nuts & bolts query ? > > Connect query analyzer to the appropriate database server. Select the > appropriate table from the dropdown menu. Paste the following into the > analyzer: > > SELECT m.company > FROM members m, > member_categories d > WHERE m.member_id = '13' AND > m.member_id = d.member_id > > From the "Query" menu, select "Show Execution Plan". Run the query. > Assuming these are the actual table and field names, it should return > your 4 records. You'll see a tab at the bottom of the results pane > called "Estimated Plan". Click on that. You'll see that it's running a > > "nested loop/inner join". > > --Jeff > > On 5/16/2005 5:02 PM, Tim Laureska wrote: > > >>Never used the query analyzer before... How do you "dump them into > > query > >>analyzer" - I just copied the query into the window of the analyzer > > and > >>removed the CF tags, but got a bunch of errors... what's the best way > > to > >>utilize the query analyzer for a baic query like this? >> >>-----Original Message----- >>From: Jeff Langevin [mailto:[EMAIL PROTECTED] >>Sent: Monday, May 16, 2005 4:39 PM >>To: CF-Talk >>Subject: Re: nuts & bolts query ? >> >>The first query creates an inner join between the two tables. Try >>dumping them into query analyzer and take a look at the execution > > plan. > >> It'll give you a better idea of what its doing with your queries. >> >>--Jeff >> >> >>On 5/16/2005 3:45 PM, Tim Laureska wrote: >> >> >> >>>I must be losing it....Why do these two queries return different sets >> >>of >> >> >>>results when we're only selecting records from one table (there is >> >>only >> >> >>>one company in the members table with a member_id equal to the >>>session.member_id value of 13 (using sql server 2000): >>> >>>1) THIS YIIELDS AN OUTPUT OF 4 RECORDS >>><cfquery datasource="#master_db#" name="test"> >>>Select m.company >> >>>FROM members m, member_categories d >> >>>where m.member_id=#session.member_id# AND m.member_id=d.member_id >>></cfquery> >>> >>><cfoutput query="test">#company#</cfoutput> >>> >>>2) THIS YIIELDS AN OUTPUT OF 1 RECORD >>><cfquery datasource="#master_db#" name="test"> >>>Select m.company >> >>>FROM members m >> >>>where m.member_id=#session.member_id# >>></cfquery> >>> >>><cfoutput query="test">#company#</cfoutput> >>> >>>The table data looks like this: >>> >>>MEMBERS table: >>>1 record with the primary key being "member_id" - one record with a >>>member id of "13" >>> >>>MEMBER_CATEGORIES table: >>>A foreign key field called "member_id" with 4 records having a >> >>member_id >> >> >>>of "13" >>> >>> >>> >>> >>> >>> >>> >>>Tim >>> >>> >>> >>> >> >> >> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:206865 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
