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
You're trying to do completely unrelated sums. The join really has no relevance to this (you're not *really* trying to join records). The only way to do this in one query (I think) is to use subqueries, which isn't in mysql yet. I'd suggest doing multiple queries.
-- -- paperCrane <Justin Patrin>
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
