左连接的Mysql查询问题

Below is my query :

SELECT 
    u.id, 
    CONCAT(u.FirstName, ' ', u.LastName) as customer, 
    u.MobileNumber, 
    u.EmailId, 
    (
        pmt.TotalCreditedAmt - trns.TotalDeductAmt
    ) as available_balance, 
    DATE_FORMAT(i.InvoiceDate, '%Y/%m/%d') as InvoiceDate, 
    pmt.TotalCreditedAmt, 
    trns.TotalDeductAmt 
FROM 
    (`Users` as u) 
    INNER JOIN (
        SELECT 
            Max(InvoiceDate) AS InvoiceDate, 
            Customer_id 
        FROM 
            Invoice 
        GROUP BY 
            Customer_id
    ) as i ON `i`.`Customer_id` = `u`.`Id` 
    LEFT JOIN (
        SELECT 
            sum(CreditedAmount) AS TotalCreditedAmt, 
            UserId 
        FROM 
            Payment 
        where 
            PaymentSucc = "Yes" 
        GROUP BY 
            UserId
    ) as pmt ON `pmt`.`UserId` = `u`.`id` 
    LEFT JOIN (
        SELECT 
            sum(Amount) AS TotalDeductAmt, 
            UserId 
        FROM 
            Transaction 
        GROUP BY 
            UserId
    ) as trns ON `trns`.`UserId` = `u`.`id` 
WHERE 
    `u`.`UserType` = 'User' 
    AND `pmt`.`TotalCreditedAmt` - trns.TotalDeductAmt < '0' 
ORDER BY 
    `u`.`EmailId` desc 

I have issue in one case as below:

user ids(1,2,3,4,5,6,7) from this user id's user id (1,2,3,4) have done their payment.and user id(5,6,7) have not done payment yet.so the entry of user id(5,6,7) not present in payment table.

and user ids(1,2,3,4,5,6,7) have done transaction and their entries are present in transaction table.and also invoice are generated.

from above query I got only record for user ids(1,2,3,4) because it's entries present in payment table. I also want records who's entries are not present in payment table. Please any person can help me to resolve my issue?

if you add the a condition in where clause related to a left joined table you obtain a inner join

 WHERE 
`u`.`UserType` = 'User' 
AND `pmt`.`TotalCreditedAmt` - trns.TotalDeductAmt < '0' //this should be place in on clause  

eg:

  ) as trns ON `trns`.`UserId` = `u`.`id`  
       and  `pmt`.`TotalCreditedAmt` - trns.TotalDeductAmt < '0' 
 WHERE  `u`.`UserType` = 'User' 

If I am understanding your question correctly, you need to use this query for pmt. The idea is to return 0 for user which are not there in Payment table.

Run this query separately and check if you are getting TotalCreditedAmt correctly or not. Later you can plug it in main query.

(
SELECT 
    sum(case when p.userid is null then 0 else p.CreditedAmount) AS TotalCreditedAmt, 
    u.Id 
FROM 
    Users u left join Payment p on p.Userid=u.id
where 
    p.PaymentSucc = "Yes" 
GROUP BY 
    u.Id
)