Unless you are using 4.1 (which allows subqueries), you should perform 3
queries, the first 2 creating temporary tables:
- sum
- difference
- left join
Ignatius
_
- Original Message -
From: "rogue" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 19, 2004 04:24
Subject: [PHP-DB] SUM and JOIN together
> Hi all,
>
> Not sure how to handle this situation and was hoping someone with a
> bigger brain then me could help.
>
> 2 tables
>
> tbl_hoursbought
>
> id | client | hours
>
> 1 | test | 5
> 2 | test | 10
>
>
> tbl_hoursused
>
>
> id | client | hours
>
> 1 | test | 3
>
>
> What I am trying to do is sum the hours bought and the hours used, then
> subtract the values to get the available hours. The problem I am having
> is that I get a multiple (based on how many rows are in the first
> table) of the value in the second table. Here is my query:
>
> select sum(tbl_hoursbought.hours) as bought, sum(tbl_hoursused.hours)
> as used
> from tbl_hoursbought
> left join tbl_hoursused
> on tbl_hoursbought.client = tbl_hoursused.client
> and tbl_hoursbought.client = $id
>
>
> So my results return 15 for bought (which is correct) but I get 6 for
> used since there are 2 rows in bought (I think). I figure I could just
> divide that by the number of results, but is there a 'correct' way of
> doing it in the SQL statement?
>
> Thanks for any help.
> Please copy me with any replies as I am on the digest.
>
> thanks,
> rogue
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
