I hear ya & see that your query works, but really don't see why the query I used would output 4 lines of the same company when there's only 1 record in the members table with that member_id - what piece am I missing on that?
-----Original Message----- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 4:14 PM To: CF-Talk Subject: Re: nuts & bolts query ? Yep, your logic is off. If you want to collapse that data, just do a distinct clause. But, you're then asking your database to do more work than necessary. If you just want the company, select only from the company table. The only reason you would need to join them is if you only want companies with categories. <cfquery datasource="#master_db#" name="test"> Select distinct m.company FROM members m INNER JOIN member_categories d on m.member_id = d.member_id where m.member_id=#session.member_id# </cfquery> ----- Original Message ----- From: "Tim Laureska" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, May 16, 2005 3:19 PM Subject: RE: nuts & bolts query ? >I was thinking this would only pull one record since the select > statement only identified the members table , which had only one > matching record .. apparently my logic is off base?? > > -----Original Message----- > From: Joe Rinehart [mailto:[EMAIL PROTECTED] > Sent: Monday, May 16, 2005 3:36 PM > To: CF-Talk > Subject: Re: nuts & bolts query ? > > Tim, > > You're getting four records because member_id 13 is associated with > four member_categories. What is the result you're trying to get? > > -Joe > > On 5/16/05, Tim Laureska <[EMAIL PROTECTED]> 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 >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:206796 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
