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:206803
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
