On Thu, 16 Aug 2001, Rodney Moses wrote:
> Thanks for the reply Philip!
>
> Unfortunately that doesn't work. I should have clarified that there never
> are null values in the 'amount' fields.
Even if you don't have NULL values in the 'amount' fields, doing the LEFT
JOIN will cause NULL values to appear. Here's an example:
Table o:
id amount
1 5.00
2 6.00
Table p:
id amount
2 4.00
3 7.00
If you join tables o and p, you'll get this:
id o.amount p.amount
1 5.00 NULL
2 6.00 4.00
3 NULL 7.00
That's where the NULL values come from; table p didn't have an amount for
id=1, and table o didn't have an amount for id=3.
Maybe you can get some insight into what is going on if you try running
this query:
SELECT a.id, o.amount, p.amount
FROM accounts AS a
LEFT JOIN orders AS o ON a.id = o.account_id
LEFT JOIN payments AS p ON a.id = p.account_id
GROUP BY a.id;
This will show you how it's doing those joins.
> Maybe it is wrong to join both the payment and the order tables to the
> account table in the query as neither of these directly related to
> each other.
BTW, maybe it's just because you over-simplified your example, but if you
really only had an "id" and an "amount" field, then you could combine
orders and payments into one table, and use negative amount for order, and
positive amount for payment.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
