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 :) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:175545 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
