mysql - INNER JOIN on 3 tables with SUM() -

i having problem trying join across total of 3 tables:

• table users: userid, cap (adsl bandwidth)
• table accounting: userid, sessiondate, used bandwidth
• table adhoc: userid, date, amount purchased

i want have 1 query returns set of users, cap, used bandwidth month , adhoc purchases month:

< table 1 ><table2><table3> user   | cap | adhoc | used marius | 3   | 1     | 3.34 bob    | 1   | 2     | 1.15 (simplified) 

here query working on:

select         `msi_adsl`.`id`,         `msi_adsl`.`username`,         `msi_adsl`.`realm`,         `msi_adsl`.`cap_size` cap,         sum(`adsl_adhoc`.`value`) adhoc,         sum(`radacct`.`acctinputoctets` + `radacct`.`acctoutputoctets`) used         `msi_adsl` inner join         (`radacct`, `adsl_adhoc`) on         (concat(`msi_adsl`.`username`,'@',`msi_adsl`.`realm`)             = `radacct`.`username` , `msi_adsl`.`id`=`adsl_adhoc`.`id`)          `canceled` = '0000-00-00' ,         `radacct`.`acctstarttime` between         '2010-11-01' ,         '2010-11-31' ,         `adsl_adhoc`.`time` between         '2010-11-01 00:00:00' ,         '2010-11-31 00:00:00' group         `radacct`.`username`, `adsl_adhoc`.`id` limit 10 

the query works, returns wrong values both adhoc , used; guess logical error in joins, can't see it. appreciated.

your query layout spread out taste. in particular, between/and conditions should on 1 line each, not 5 lines each. i've removed backticks, though might need them 'time' column.

since table layouts don't match sample query, makes life difficult. however, table layouts include userid (which sensible), i've written query relevant joins using userid. noted in comment, if design makes necessary use concat operation join 2 tables, have recipe performance disaster. update actual schema tables can joined userid, table layouts suggest should possible. obviously, can use functions results in joins, (unless dbms supports 'functional indexes' , create appropriate indexes) dbms won't able use indexes on table function evaluated speed queries. one-off query, may not matter; production queries, matter lot.

there's chance job want. since aggregating on 2 tables, need 2 sub-queries in clause.

select u.userid,        u.username,        u.realm,        u.cap_size cap,        h.adhoc,        a.octetsused   msi_adsl u   join (select userid, sum(acctinputoctets + acctoutputoctets) octetsused           radact          acctstarttime between '2010-11-01' , '2010-11-31'          group userid        )    on a.userid = u.userid   join (select userid, sum(value) adhoc           adsl_adhoc          time between '2010-11-01 00:00:00' , '2010-11-31 00:00:00'          group userid        )    h on h.userid = u.userid  u.canceled = '0000-00-00'  limit 10 

each sub-query computes value of aggregate each user on specified period, generating userid , aggregate value output columns; main query pulls correct user data main user table , joins aggregate sub-queries.