php - Mysql多连接查询记录问题

Question

下面是我的查询，

SELECT 
    u.id, 
    CONCAT(u.FirstName, ' ', u.LastName) as customer, 
    (
        (
            CASE WHEN pmt.TotalCreditedAmt IS NOT NULL THEN pmt.TotalCreditedAmt ELSE 0 END
        ) - (
            CASE WHEN trns.TotalDeductAmt IS NOT NULL THEN trns.TotalDeductAmt ELSE 0 END
        )
    ) as closing_balance 
FROM 
    (`Users` as u) 
    JOIN `UserAddresses` as uaddresses ON `u`.`Id` = `uaddresses`.`UserId` 
    AND (
        uaddresses.PreferredPickUpAddress = "Yes"
    ) 
    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 `uaddresses`.`PincodeId` IN ('10369', '10372', '10309') 
ORDER BY 
    `u`.`id` desc 

从上面查询用户地址表条件我的用户表记录是80

从这80条记录中payment table记录是31条，transaction table entry是9条，在这个表里group by query

从这两个表(支付和交易)中，唯一用户数是 34，这意味着一些用户正在付款，一些用户正在交易，结果总的不同用户是 80 名用户中的 34 名。

我需要的结果是 34 个用户，但它给了我 80 个用户

谁能帮我解决这个问题。

Answer 1

使用 Join 而不是 Left join 应该可以解决您的问题。