I have three tables, to model a purchasing system: transaction number client date
purchased_product transaction product price payed_amount transaction date amount This model allows me to have a transaction with multiple products and also record several payments on the trasaction, so a client can pay just a fraction of the pruchased total and come later and pay the rest. However creating a debt report in a single query is somehow dificult. I know how to do it with views in Oracle, but mySQL seems lack from them. If I relate the three tables in one query, some quantities appear multiple times. Suppose we have one transaction with two products and one payment. transaction number client date 1 Joe Doe June 10th purchased_product transaction product price 1 glass 5 1 spoon 3 payed_amount transaction date amount 1 June 10th 8 And the query in the regular way to do it is: Select * >From transaction, purchased_product, payed_amount Where transaction.number = purchased_product.transaction And transaction.number = payec_amout.transaction Of course this gives me the payed amount twice. What I need to do at the end is a report with all the clients that own to the store. Any ideas? Ernesto
