How to use Inner Joins with where clause?-CodePudding

I am here posting some extension of How to concatenate more sql queries to get one table? question to get more specific query results.

Let assume we are having now this type of dataset.

query1 : select * from userdetails1.UserTable;

Result1:                
UserID  Username    Status
1         John      Active
2         Micheal   Active
3         Ann       Active
4         Shasha    Resigned
5         mike      Resigned

query2 : 
select * 
from userdetails2.AmountTable 
where TotalAmountResetDate ='2022-01-31' ;

result2:
UserId  TotalAmount TotalAmountResetDate
1          -12         1/31/2022
2          345         1/31/2022
3          213         1/31/2022
4          0           1/31/2022
5          0           1/31/2022

query3:
select Username,sum(TotalInvested) as TotalInvested 
from userdetails2.TotalInvestmentsTable 
where total_invested_date between '2022-01-31' and '2022-02-12'
group by Username;

result3:

Username    Totalnvested
John         45000
Micheal      45000
Ann          45000

query4:
select Username,sum(TotalExpenses) 
from userdetails2.TotalExpensesTable 
where date_of_expense_date between '2022-01-31' and '2022-02-12'
group by Username;

Username    TotalExpenses
John           2543
Micheal        2345
Ann            3290

Then how I get the output as below,

UserId  Username    TotalAmountResetDate    TotalAmount Totalnvested    TotalExpenses   TotalBalance
1        John              1/31/2022               -12       45000            2543          42445
2        Micheal           1/31/2022               345       45000            2345          43000
3        Ann               1/31/2022               213       45000            3290          41923
4        Shasha            1/31/2022               0         0                 0            0
5        mike              1/31/2022               0         0                 0            0

From the answers I have made this answer, but seems it is not getting what I need, it removes Zero values. Can someone help?

    SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,   
       t2.TotalAmount,   
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount   COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username
where t2.TotalAmountResetDate='2022-01-31' and investment.total_invested_date between '2022-01-31' and '2022-02-12';

CodePudding user response：

You might want to let the condition move to ON from WHERE because you are doing OUTER JOIN

SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,   
       t2.TotalAmount,   
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount   COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username AND 
investment.total_invested_date BETWEEN '2022-01-31' and '2022-02-12'
WHERE t2.TotalAmountResetDate='2022-01-31';

CodePudding user response：

Apologies, edited as I misread the question...

I would just move the date parameter from the where clause to the join on 'TotalInvestmentsTable' as below:

SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,   
       t2.TotalAmount,   
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount   COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
INNER JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
and investment.total_invested_date between '2022-01-31' and '2022-02-12'
INNER JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username
where t2.TotalAmountResetDate='2022-01-31';

CodePudding user response：

Simply outer join the queries to your user table.

select
  u.username,
  a.totalamount,
  a.totalamountresetdate,
  coalesce(i.total, 0) as totalinvested,
  coalesce(e.total, 0) as totalexpenses,
  a.totalamount   coalesce(i.total, 0) - coalesce(e.total, 0) as totalbalance
from userdetails1.usertable u
left join userdetails2.amounttable a 
            on a.userid = u.userid
           and a.totalamountresetdate = date '2022-01-31'
left join
(
  select username, sum(totalinvested) as total 
  from userdetails2.totalinvestmentstable 
  where total_invested_date between date '2022-01-31' and date '2022-02-12'
  group by username
) i on i.username = u.username
left join
(
  select username, sum(totalexpenses) as total
  from userdetails2.totalexpensestable 
  where date_of_expense_date between date '2022-01-31' and date '2022-02-12'
  group by username
) e on e.username = u.username
order by u.username;

(It seems a bad idea by the way to use the username instead of the userid for the investment and expense tables. What do you have the userid for then?)