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 -----Original Message----- From: Vivec [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 9:54 AM To: CF-Community Subject: Tricky SQL Joins :( Hello all. I have 4 tables. Registration (RegID,Name) Costumes(Costid,Costume Name) Reg_Costumes(RCID,RegID,CostID) And Payments(PayID,CostID,Amount) Now , of course there can be several costumes for each registration. Hence the Reg_Costumes table, to link them all together. In the Payments Table , I have the CostID.And of course there can be multiple Payments for each costume. Now..the tricky part is..that I want to search on multiple fields, from multiple tables. If I stick to Registration,Costumes, and Reg_Costumes everything works just fine. But I need to limit the Results to either only contain Costumes that have been Paid for, OR to just show all the costumes regardless of whether they are paid for or not. But, because there are multiple Payments in the Payments table, I get multiple returns for the same Costume in the Results. I've tried several join types, but nothing removes the 'duplicate' rows when there is more than one payment in the Payments table for a specific Costume ID. I know there has to be an SQL solution, but I just can't figure it out :-\ Can anyone who is more experienced assist? Thanks :) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:5:175549 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=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
