I am trying to make a query for getting data from 3 tables. Here are the sample tables:
Table 1:
| id | user_id | date | amount |
|---|---|---|---|
| 1 | 1 | 15.01.2021 | 1000 |
| 2 | 1 | 16.01.2021 | 1000 |
| 3 | 2 | 17.01.2021 | 1000 |
| 4 | 1 | 17.01.2021 | 1000 |
| 5 | 3 | 22.01.2021 | 1000 |
Table 2:
| id | user_id | date | amount |
|---|---|---|---|
| 1 | 1 | 17.01.2021 | 50 |
| 2 | 1 | 20.01.2021 | 50 |
| 3 | 2 | 21.01.2021 | 100 |
| 4 | 1 | 21.01.2021 | 75 |
Now I need to make a query that can give the following output using these 2 tables:
Result:
| user_id | date | T1.amount | T2.amount |
|---|---|---|---|
| 1 | 15.01.2021 | 1000 | NULL |
| 1 | 16.01.2021 | 1000 | NULL |
| 1 | 17.01.2021 | 1000 | 50 |
| 2 | 17.01.2021 | 1000 | NULL |
| 1 | 20.01.2021 | NULL | 50 |
| 2 | 21.01.2021 | NULL | 100 |
| 1 | 21.01.2021 | NULL | 75 |
| 3 | 22.01.2021 | 1000 | NULL |
I have tried with Join and Union but no luck. Thanks in Advance
CodePudding user response：
Please try this,
SELECT
A.USER_ID
,A.DATE
,B.AMOUNT AS AMOUNT_1
,C.AMOUNT AS AMOUNT_2
FROM
(
SELECT USER_ID
,DATE
FROM TABLE_1
UNION
SELECT USER_ID
,DATE
FROM TABLE_2
)A
LEFT JOIN
TABLE_1 B
ON A.USER_ID=B.USER_ID AND A.DATE=B.DATE
LEFT JOIN
TABLE_2 C
ON A.USER_ID=C.USER_ID AND A.DATE=C.DATE
GROUP BY A.USER_ID
,A.DATE
, B.AMOUNT
,C.AMOUNT
CodePudding user response：
Union first providing dummy columns then group by
with cte as
(
select user_id,date,amount amt1 , null amt2 from t
union all
select user_id,date,null amt1 , amount amt2 from t1
)
select user_id,date,sum(amt1),sum(amt2)
from cte
group by user_id,date;