YOu could do a sub-query with the payments table...something like: WHERE... AND costumeid IN (select costumeid FROM payments where paid = 1)
On 9/30/05, Vivec <[EMAIL PROTECTED]> wrote: > > 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:175546 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
