select and sum from two tables

[tirso]

hi to all

Ihave a main table with unique "id" with relation in table1 and table2 "main_id". I want to query and ouput the information from main table including the total of debitval from table1 and creditval from table2. Any help would greatly appreciated.

Thanks in advance.

maintable

id payee description reference_no
1 john advance 1
2 dave request 2

table1

id main_id debitval
1 1 5.00
2 1 10.00
3 2 6.00
4 2 7.00

table2

id main_id creditval
1 1 5.00
2 1 10.00
3 1 15.00
4 2 4.00

output table should be like this

id payee description reference_no debitval creditval
1 john advance 1 15.00 30.00
2 dave request 2 13.00 4.00

[Dan Grossman]

SELECT
  maintable.id,
  SUM(table1.debitval) AS `debitval`,
  SUM(table2.creditval) AS `creditval`
FROM
  maintable
LEFT OUTER JOIN
  table1
ON 
  maintable.id = table1.main_id
LEFT OUTER JOIN
  table2
ON
  maintable.id = table2.main_id
GROUP BY 
  maintable.id

[tirso]

hi Dan Grossman,

Thank for your immediate replied. It work's like a charm.

Best Regards,
Tirso

[r937]

sorry, dan, your query produces these results:

id  debitval  creditval
 1    45.0       60.0
 2    13.0        8.0

your results are inflated because of cross join effects

this query --

SELECT maintable.id
     , maintable.payee 
     , maintable.description 
     , maintable.reference_no
     , d.total_debit
     , c.total_credit
  FROM maintable
LEFT OUTER 
  JOIN ( SELECT main_id
              , SUM(debitval) AS total_debit
           FROM table1
         GROUP
             BY main_id ) AS d
    ON d.main_id = maintable.id
LEFT OUTER 
  JOIN ( SELECT main_id
              , SUM(creditval) AS total_credit
           FROM table2
         GROUP
             BY main_id ) AS c
    ON c.main_id = maintable.id

produces the correct results --

id  payee  description  reference_no  total_debit  total_credit
 1  john   advance          1            15.0          30.0
 2  dave   request          2            13.0           4.0

[tirso]

hi r937,

I'll notice a while a go that dan solution produce incorrect result. But somehow it was help me a lot.

Thank you r937.

Tirso