CODEWITHSUNDEEP
mysqlsql
Ahmed Guure
Ahmed Guure

Reputation: 23

Join three tables and subtract sum of column from a column data

I have three tables accounts, receivables and receiveds .

My basic table is receivables I want to get the names from the account table and then subtract the sum of received column in receiveds table form the receivable column in receivables table with same ref numbers. i want to ignore if the balance is zero.

accounts

id name
22 John
23 Kahn
24 Falis
25 Haseni
26 Gent

receivables

id receivable Ref
22 70 A1
24 100 A2
26 60 A3
24 15 A4

receiveds

ref id received
A1 22 30
A2 24 60
A1 22 40
A3 26 20
A2 24 10

desired results

id name ref receivable received balance total_id_balance
24 Falis A2 100 70 30 45
26 Gent A3 60 20 40 40
24 Falis A4 15 0 15 45

I have tried this code but it's not working for me

SELECT * 
FROM receivables AS rvb 
  LEFT JOIN accounts AS acc 
    ON rvb.id = acc.id 
  LEFT JOIN (SELECT SUM(received) as sum_rvd FROM receiveds) AS rvd 
    ON acc.id = rvd.id where rvb.receivable>rvd.sum_rvd

Upvotes: 1

Views: 116

Answers (3)

Jared
Jared

Reputation: 1374

INNER JOINs filter out NULL values and the HAVING clause removes zero-balance rows.


SELECT 
    accounts.* ,
    ra.receivable,
    rd.received,
    ra.receivable - rd.received as balance
FROM accounts
    INNER JOIN ( SELECT id, SUM(receivable) as receivable FROM receivables GROUP BY id ) ra 
        ON ra.id = accounts.id
    INNER JOIN ( SELECT id, SUM(received) as received FROM receiveds GROUP BY id ) rd 
        ON rd.id = accounts.id
HAVING balance > 0

UPDATE

With a ref field we just need to add it as another grouping field for subselects, and change second INNER JOIN to LEFT JOIN (actually looks like it was a mistake from the start, coz we missed entries without receiveds that still had positive balance). Also changing received field to a COALESCE to get zeroes instead of NULL's (indicating non existing rows in a receiveds table). Since you need an overall per-id total_id_balance field, the natural way to grab it - is using window functions. Note, they are supported only for MySQL 8.0+. So the resulting query looks like this:

SELECT 
    accounts.* ,
    ra.ref,
    ra.receivable,
    COALESCE(rd.received, 0) as received,
    ra.receivable - COALESCE(rd.received, 0) as balance,
    SUM( ra.receivable - COALESCE(rd.received, 0) ) OVER ( PARTITION BY id ) as total_id_balance
FROM accounts
    INNER JOIN ( SELECT id, SUM(receivable) as receivable, ref FROM receivables GROUP BY id, ref ) ra 
        ON ra.id = accounts.id
    LEFT JOIN ( SELECT id, SUM(received) as received, ref FROM receiveds GROUP BY id, ref ) rd 
        ON 
            rd.id = accounts.id 
            AND 
            ra.ref = rd.ref
HAVING balance > 0
ORDER BY ref

Upvotes: 1

user1191247
user1191247

Reputation: 12973

Assuming it is possible to have an id in receivables without a corresponding id in receiveds, the second join needs to be a LEFT JOIN and you need to handle the NULLs in your SELECT list -

SELECT 
    a.*,
    ra.receivable,
    IFNULL(rd.received, 0) received,
    ra.receivable - IFNULL(rd.received, 0) balance
FROM accounts a
INNER JOIN (
    SELECT id, SUM(receivable) receivable
    FROM receivables
    GROUP BY id
) ra 
    ON a.id = ra.id
LEFT JOIN (
    SELECT id, SUM(received) received
    FROM receiveds
    GROUP BY id
) rd 
    ON a.id = rd.id
HAVING balance > 0;

You can (and should) go a step further and remove the first derived table as it is unnecessary overhead -

SELECT
    a.*,
    SUM(ra.receivable) receivable,
    IFNULL(SUM(rd.received), 0) received,
    SUM(ra.receivable) - IFNULL(rd.received, 0) balance
FROM accounts a
INNER JOIN receivables ra
    ON a.id = ra.id
LEFT JOIN (
    SELECT id, SUM(received) received
    FROM receiveds
    GROUP BY id
) rd
    ON a.id = rd.id
GROUP BY id
HAVING balance > 0;

db<>fiddle

Upvotes: 0

Yosef.Schwartz
Yosef.Schwartz

Reputation: 126

This work for me -

EDIT- Add support for non-unique id in receivables

SELECT acc.id ,acc.name, res.receivable, res.received, (res.receivable - res.received) AS balance
FROM accounts AS acc JOIN 
    (SELECT recv.id, recv.receivable, rec.received 
     FROM (
         SELECT id, SUM(receivable) AS receivable
         FROM receivables
         GROUP BY id) AS recv JOIN 
            (SELECT id, SUM(received) AS received 
             FROM receiveds 
             GROUP BY id) AS rec ON rec.id = recv.id 
         WHERE rec.received < recv.receivable) AS res ON res.id = acc.id;

Upvotes: 0

Related Questions