LEFT OUTER JOIN SUM doubles problem
- by Michael
Hi I've got two tables:
Table: Shopping
shop_id shop_name shop_time
1 Brian 40
2 Brian 31
3 Tom 20
4 Brian 30
Table:bananas
banana_id banana_amount banana_person
1 1 Brian
2 1 Brian
I now want it to print:
Name: Tom | Time: 20 | Bananas: 0 Name: Brian | Time: 101 | Bananas: 2
I used this code:
$result = dbquery("SELECT tz.*, tt.*,
SUM(shop_time) as shoptime,
count(banana_amount) as bananas
FROM shopping tt
LEFT OUTER JOIN bananas tz ON tt.shop_name=tz.banana_person
GROUP by banana_person
LIMIT 40
");
while ($data5 = dbarray($result)) {
echo 'Name: '.$data5["shop_name"].' | Time: '.$data5["shoptime"].' | Bananas: '.$data5["bananas"].'<br>';
}
The problem is that I get this instead:
Name: Tom | Time: 20 | Bananas: 0
Name: Brian | Time: 202 | Bananas: 6
I just don't know how to get around this.
