select and sum from two tables

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

Code:

---

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

---

hi Dan Grossman,

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

Best Regards,
Tirso

sorry, dan, your query produces these results:

Code:

---

id  debitval  creditval
 1    45.0      60.0
 2    13.0        8.0

---

your results are inflated because of cross join effects

this query --

Code:

---

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 --

Code:

---

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

---

:)

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