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 purchased total and come later and pay the rest. However creating a debt report in a single query results 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. Is my model wrong?, Any ideas, please Ernesto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
