CODEWITHSUNDEEP
mysqlsql
sadi
sadi

Reputation: 25

Can't find out expected result from Left Join Where & group by

I have two table receivable & receive.

table: receivable

tranId  roll     month         amount
 1      1111    October-2019    10
 2      1112    October-2019    10
 3      1113    October-2019    10
 4      1114    October-2019    10
 5      1115    October-2019    10

Table: receive

tranId   roll       month       amount
1       1111    October-2019    10
2       1111    September-2019  10
3       1113    October-2019    10
4       1114    October-2019    10
5       1115    October-2019    10
6       1116    October-2019    10
7       1117    October-2019    10
8       1118    October-2019    10
9       1119    October-2019    10
10      1120    October-2019    10

In both table month columns are string & table receive roll+month column is unique. i want to create a receivable & receive statement report of a student (Where roll=1111) from those table like.

Expected result:

month           rcvamount   rcvvmonth      rcvvamount
---------------------------------------------------------
October-2019       10       October-2019    10
---------------------------------------------------------
September-2019     10           -                -

my query is:

SELECT receive.month, sum(receive.amount) AS rcvamount, receivable.month AS rcvvmonth,
       receivable.amount AS rcvvamount
FROM receive
    LEFT JOIN receivable ON receive.month = receivable.month
WHERE receive.roll = 1111
GROUP BY receive.month

and resutl is:

month           rcvamount   rcvvmonth      rcvvamount
----------------|-------|----------------------------------
October-2019    |   50  |       October-2019    10
----------------|-------|----------------------------------
September-2019     10           -                -

revamount in October-2019 should be 10 from receive table.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can aggregate before joining. Or use union all and aggregate:

SELECT month, SUM(ramount) AS rcvamount, 
       SUM(raamount) AS rcvvamount
FROM ((SELECT month, amount as ramount, null as raamount
       FROM receive
       WHERE roll = 1111
      ) UNION ALL
      (SELECT month, NULL as ramount, amount as raamount
       FROM receiveable
       WHERE roll = 1111
      )
     ) r
GROUP BY month;

One nice feature of UNION ALL is that it handles missing data in either table.

Upvotes: 1

Related Questions