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
)