Gel, You can make the whole thing a subquery for your final "outer" query.
So.... SELECT distinct (unique fields here that you actually want to display) FROM (convoluted query here that returns multiple rows per costume) On 9/30/05, Vivec <[EMAIL PROTECTED]> wrote: > > Well, > > The tables have a lot more fields than displayed. Registration has the > standard fields like Fname, Lname, Address1, address2, city etc. > > And those are some of the fields we want to search on. > And then Costumes has > > Costume Section, Gender and those are some of the fields we want to > search on as well. > > And then the payments table has the amount that was paid for the > costume so far. So there can be several payments made until a costume > is fully paid for. > > All I included in the previous post were the ID fields. > The joins are now horribly complex in my view. > > Almost unreadable: > FROM (REGISTRATION INNER JOIN REG_PROFILE ON REGISTRATION.RG_ID = > REG_PROFILE.RP_RG_ID) INNER JOIN (REG_PROFILE_PAYMENTS RIGHT JOIN > PROFILES_0003_COSTUMES ON REG_PROFILE_PAYMENTS.RPP_EX_ID = > PROFILES_0003_COSTUMES.PR_ID) ON REG_PROFILE.RP_PR_ID = > PROFILES_0003_COSTUMES.PR_RP_ID > > I can use CFOUTPUT GROUP to group on pr_CostumeID so that only one > costume ID shows up in the listing, and then stick a <Cfset > counter=counter+1> inside the Cfoutput. So now only one COstumeID > shows up, but I would really like to limit to just one CostumeID in > the resultset inside the SQL itself...somehow. > > On 9/30/05, Matthew Small <[EMAIL PROTECTED]> wrote: > > Can you explain a little better for me? > > > > What would the result of your search look like, and what are you > searching > > for? > > > > > > Matthew Small > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:5:175552 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
