LEFT OUTER JOIN SUM doubles problem

Posted by Michael on Stack Overflow See other posts from Stack Overflow or by Michael
Published on 2011-01-09T14:36:57Z Indexed on 2011/01/09 14:53 UTC
Read the original article Hit count: 539

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about php

Related posts about sql