No exactly Peter.
What I want is a single report that tell me the debt per transaction.
Something like
Transaction Purchased Payed Debt
1 8 8 0
so a "Group by" operation must be involved.
The main problem is that if I join the three tables with the query you wrote I get
something like (i took some fields off for this discussion)
Transaction purchased_product.price payed_amount.amount
1 5 8
1 3 8
And as you can see the payed amout appears twice. If we involve the "Group by"
operation with that query we'll get an incorrect result; the amount payed will be 16
instead of 8.
I believe that the query result, before the "Group by" operation must be something like
Transaction purchased_product.price payed_amount.amount
1 NULL 8
1 5 NULL
1 3 NULL
after all, purchased_product and payed_amount are not related directly.
In order to solve this with views I first create a view with the total by transaction
from purchased_product, then a view with the total by transaction from payed_amout and
finally join transaction with both views, but I haven't seen support for views in
mySQL. The problem can also be solved with subqueries or writing a PHP or Pearl
script. However I'm using a generic SQL reporting tool, so my only chance is to use
pure SQL instructions.
----- Original Message -----
From: Peter Brawley
To: Ernesto Silva ; [EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 1:10 AM
Subject: Re: Joining data
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]
