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

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

accounts

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

receivables

id	receivable
22	70
24	100
26	60

receiveds

id	received
22	30
24	60
22	40
26	20
24	10

desired results

id	name	receivable	received	balance
24	Falis	100	70	30
26	Gent	60	20	40

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

CodePudding user response：

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

CodePudding user response：

You can use a GROUP BY clause to sum the received values per id in the receiveds table, and then join that result to the receivables and accounts tables. Here is an example query that should produce the desired results:

        SELECT 
        acc.id, 
        acc.name, 
        rvb.receivable, 
        COALESCE(rvd.received_sum, 0) AS received, 
        rvb.receivable - COALESCE(rvd.received_sum, 0) AS balance
    FROM receivables AS rvb
    LEFT JOIN accounts AS acc
        ON rvb.id = acc.id
    LEFT JOIN (
        SELECT id, SUM(received) AS received_sum
        FROM receiveds
        GROUP BY id
    ) AS rvd
        ON acc.id = rvd.id
    WHERE rvb.receivable > COALESCE(rvd.received_sum, 0);

CodePudding user response：

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;