mysql-具有多个条件的左联接表

SELECT
    *
FROM
    u_data ud
JOIN
    u_contracts uc
ON
    uc.user_id = ud.id
LEFT JOIN
    u_transactions ut
ON
    ut.contract_id = uc.id
    /* AND DATE( ut.add_timestamp ) > DATE( ud.suspicious ) */
WHERE
    ud.suspicious > 0
    AND ud.suspicious != ''
    AND ud.suspicious IS NOT NULL
    AND uc._status = 6
GROUP BY
    ud.id;

这给了我正确的行数,但是我还需要检查事务日期是否大于第一个表中的可疑日期.一旦添加该条件(也在开始位置尝试过),它将消除所有没有交易日期的字段.

意思是,如果我左联接那些表,我将得到如下结果：

+-----+----------+---------------+---------------+--------+
| row | original | ud.suspicious | add_timestamp | amount |
+-----+----------+---------------+---------------+--------+
|   1 |      100 | 10.01.2000    | 01.01.2000    |     50 |
|   2 |      100 | 10.01.2000    | NULL          | NULL   |
|   3 |      100 | 10.01.2000    | 12.01.2000    |     10 |
|   4 |      100 | 11.01.2000    | 12.01.2000    |     20 |
|   5 |      100 | 12.01.2000    | 12.01.2000    |     30 |
+-----+----------+---------------+---------------+--------+

现在,我基本上需要消除2000年1月1日的金额和日期,因为它早于可疑日期,因此我不需要额外的金额,但是我需要原始金额.这就是我需要得到的

+-----+----------+---------------+---------------+--------+
| row | original | ud.suspicious | add_timestamp | amount |
+-----+----------+---------------+---------------+--------+
|   1 |      100 | 10.01.2000    | NULL          | NULL   |
|   2 |      100 | 10.01.2000    | NULL          | NULL   |
|   3 |      100 | 10.01.2000    | 12.01.2000    |     10 |
|   4 |      100 | 11.01.2000    | 12.01.2000    |     20 |
|   5 |      100 | 12.01.2000    | 12.01.2000    |     30 |
+-----+----------+---------------+---------------+--------+

但是当我设置该条件DATE(ut.add_timestamp)> DATE(ud.suspicious)我得到这个：

+-----+----------+---------------+---------------+--------+
| row | original | ud.suspicious | add_timestamp | amount |
+-----+----------+---------------+---------------+--------+
|   1 |      100 | 10.01.2000    | 12.01.2000    |     10 |
|   2 |      100 | 11.01.2000    | 12.01.2000    |     20 |
|   3 |      100 | 12.01.2000    | 12.01.2000    |     30 |
+-----+----------+---------------+---------------+--------+

为什么用LEFT JOIN删除条目,如何解决此问题以获得所需的正确数据？

编辑：完整的原始工作查询：

SELECT
    ud.Pcode AS pcode
    , CONCAT( ud.Name, ' ', ud.Surname ) AS name
    , CONCAT(
        ud.Da_CityName,
        IF ( ud.Da_Street != '', CONCAT( ', ', ud.Da_Street ), '' ),
        IF ( ud.Da_Housen != '', CONCAT( ', ', ud.Da_Housen ), '' ),
        IF ( ud.Da_Flatn != '', CONCAT( ', ', ud.Da_Flatn ), '' ),
        IF ( ud.Da_PostIndex != '', CONCAT( ', ', ud.Da_PostIndex ), '' )
    ) AS address
    , uc.id AS contract_id
    , uc.terminate_date AS terminate_date
    , FORMAT( IF ( (
        SELECT
            SUM( external_account )
        FROM
            u_transactions
        WHERE
            contract_id = uc.id 
            AND nulled = 0
            AND type in ( 'penalty', 'initial', 'comission', 'penalty2', 'penalty2_vat' )
            AND DATE( add_timestamp ) > DATE( ud.suspicious )
    ) IS NULL, uc.inkasso_debt, uc.inkasso_debt - (
        SELECT
            SUM( external_account )
        FROM
            u_transactions
        WHERE
            contract_id = uc.id 
            AND nulled = 0
            AND type in ( 'penalty', 'initial', 'comission', 'penalty2', 'penalty2_vat' )
            AND DATE( add_timestamp ) > DATE( ud.suspicious )
    ) ), 2 ) AS summ
FROM
    u_data ud
JOIN
    u_contracts uc
ON
    uc.user_id = ud.id
WHERE
    ud.suspicious > 0
    AND ud.suspicious != ''
    AND ud.suspicious IS NOT NULL
    AND uc._status = 6
    AND DATE( uc.terminate_date ) < ( NOW() - INTERVAL 45 DAY )

由于太混乱了,我试图摆脱两个子选择,并加入它们以获取SUM(external_account)金额.也许这将有助于了解我的问题.

...
RIGHT JOIN u_transactions ut
ON ut.contract_id = uc.id
AND ( DATE( ut.add_timestamp ) > DATE( ud.suspicious )
      OR ut.add_timestamp IS NULL )
...

在您的查询中