Dhawal Mhatre Dhawal Mhatre - 2 months ago 8
MySQL Question

Need data from couple of table in mysql using join or subquery

I have written below query in MYSQL :

SELECT ab.id, ab.name, creditAmt, debitAmt, creditAmt-debitAmt balance FROM ab
JOIN (select sum(credit_amt) creditAmt from pq) i
JOIN (select sum(debit_amt) debitAmt from pq) e
WHERE ab.type = 'user' GROUP BY ab.user_eid


Below is My Table Structure for ab :

enter image description here

Below is My Table Structure for pq :

enter image description here

I want to show total balance available for both the ab.ID in excel or print it on webpage

Any idea or changes in MySQL query i have used?

Answer

Instead of subqueries, the more intuitive approach would be to join and group by the account. Note that you'd need to treat credit as positive number and debit as negative:

SELECT   ab.name, SUM(pq.credit - pq.debit) AS balance
FROM     ab
JOIN     pq ON ab.id = pq.ab_id
GROUP BY ab.name