Ernesto,
Are you looking for this?
SELECT *
FROM transaction
INNER JOIN purchased_product ON
transaction.number=purchased_product.transaction
INNER JOIN payed_amount ON
purchased_product.transaction=payed_amount.transaction
WHERE transaction.number=1;
PB
----- Original Message -----
From: Ernesto Silva
To: [EMAIL PROTECTED]
Sent: Monday, June 09, 2003 8:07 PM
Subject: Joining data
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]
