MySQL: Join 3 tables with distinct values-CodePudding

So I have 3 tables: tableA, tableB, tableC with structures as follows

tableA

id	name	state
1	Ray	MD
2	Sam	LA
3	John	NY

tableB

id	a_id	amount	code
1	2	10	CHARGE
2	2	20	CHARGE
3	3	70	CHARGE

tableC

id	a_id	amount	code
1	2	50	CHARGE
2	2	40	DEPOSIT
3	1	60	CHARGE

I need the output of the join as follows:

A id	amount
1	60
2	30
3	70

So, here it calculates the sum of amount based on id in tableA. It checks the tableB for amount and if not present only then it checks tableC. Thats why the id 2 has amount 30 in output.

But what actually happens is the sum of both tables are added. So I get amount 120 for id 2. How do I get the required output?

So I tried this query here

Select if( SUM(CASE WHEN B.code != 'DEPOSIT' 
                    THEN B.amount 
                    ELSE 0 END) > 0,
           SUM(CASE WHEN B.code != 'DEPOSIT' 
                    THEN B.amount 
                    ELSE 0 END),
           SUM(CASE WHEN C.code != 'DEPOSIT' 
                    THEN C.amount 
                    ELSE 0 END)) as total 
FROM tableA as A 
left join tableB AS B on A.id=B.a_id 
LEFT JOIN tableC AS C on A.id=C.a_id 
GROUP BY A.id

CodePudding user response：

Test this:

SELECT id, CASE WHEN b.amount
                THEN b.amount
                ELSE c.amount
                END amount
FROM tableA a
LEFT JOIN ( SELECT a_id id, 
                   SUM( CASE WHEN code != 'DEPOSIT'
                             THEN amount
                             ELSE 0 
                             END ) amount
            FROM tableB
            GROUP BY id ) b USING (id)
LEFT JOIN ( SELECT a_id id, 
                   SUM( CASE WHEN code != 'DEPOSIT'
                             THEN amount
                             ELSE 0 
                             END ) amount
            FROM tableC
            GROUP BY id ) c USING (id)

CodePudding user response：

You can try a solution like this

    Select A.id, SUM(if(B.amount is NOT NULL,B.amount, C.amount)) as total 
FROM tableA as A 
LEFT JOIN tableB AS B on A.id=B.a_id and B.code != 'DEPOSIT'
LEFT JOIN tableC AS C on A.id=C.a_id and C.code != 'DEPOSIT'
GROUP BY A.id