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 >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:206794 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
