CODEWITHSUNDEEP
mysqljoin
Peon
Peon

Reputation: 8020

left join tables with multiple conditions

I'm struggling with one query. I need to LEFT JOIN two tables where the specific id match and where dates differ:

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;

This gives my the right count of rows, but I also need to check, if the transaction date is greater then suspicious date from the first table. As soon as I add that condition ( tried in where at start too ), it eliminates all fields, where there is no transaction date.

Meaning, if I LEFT JOIN those tables, I get a result like this: 

+-----+----------+---------------+---------------+--------+
| 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 |
+-----+----------+---------------+---------------+--------+

Now, I basically need to eliminate the amount and date for 01.01.2000, since it is older then suspicious date and I don't need the additional amount, but I need the original though. This is what I need to get

+-----+----------+---------------+---------------+--------+
| 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 |
+-----+----------+---------------+---------------+--------+

But when I set that condition DATE( ut.add_timestamp ) > DATE( ud.suspicious ) I get this:

+-----+----------+---------------+---------------+--------+
| 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 |
+-----+----------+---------------+---------------+--------+

Why is it removing the entries with LEFT JOIN and how can I fix this to get the right data I need?

Edit: The full original working query:

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 )

As it is too messy, I'm trying to get rid of the two subselects and join them to get the SUM( external_account ) amount. Maybe this will help to understand my issue.

Upvotes: 3

Views: 18391

Answers (5)

bitflipper
bitflipper

Reputation: 21

You can't expect OR condition with OUTER join to return right results. Since you have NULL in your comparison column, it will never return 'true' for any comparison as it technically denotes 'no value'. For your case, you may need to replace NULL with value that is always true before join condition is evaluated. something like below..

LEFT JOIN u_transactions ut ON ut.contract_id = uc.id AND ( isnull(DATE(ut.add_timestamp),DATEADD(dd,1,DATE(ud.suspicious))) > DATE(ud.suspicious) )

Upvotes: 0

Ravi
Ravi

Reputation: 2464

If what you need is a NULL string in the output, add an IF statement in your select statement. 

SELECT
  IF(
     DATE( ut.add_timestamp ) < DATE( ud.suspicious ),
     'NULL',
     ud.add_timestamp
    ) AS add_timestamp
FROM
  -----Rest of the query-----

Based on your updated post, below is my version of the query. It will be very helpful if you can create the table structures in sqlfiddle.com if this query does not solve your problem.

SELECT  

  FORMAT(
     IF(                
            SUM(external_account) IS NULL,                                  
            uc.inkasso_debt,
            uc.inkasso_debt -  SUM( external_account)
    ), 2) AS summ

FROM
    u_data ud
    JOIN u_contracts uc ON uc.user_id = ud.id
    LEFT JOIN u_transactions ON (   
                               u_transactions.contract_id = uc.id                                                                        
                               AND nulled = 0                                                                          
                               AND (type in ( 'penalty', 'initial', 'comission', 'penalty2', 'penalty2_vat' ))
                               AND DATE(u_transactions.add_timestamp ) > DATE( ud.suspicious )                                                                  
                            )                                                                     


WHERE
  ud.suspicious > 0   
  AND uc._status = 6
  AND DATE( uc.terminate_date ) < ( NOW() - INTERVAL 45 DAY )

GROUP BY
  ud.id

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 59997

Try

...
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 )
...

in your query

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36601

I think It's giving NULL in u_transaction and amount because you're using AND with ON condition, try to put it in where clause and see what happens.

Try this

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

WHERE
    ud.suspicious > 0
    AND ud.suspicious != ''
    AND ud.suspicious IS NOT NULL
    AND uc._status = 6
    AND DATE( ut.add_timestamp ) > DATE( ud.suspicious )
GROUP BY
    ud.id;

Upvotes: 1

Euclides Mul&#233;mbw&#232;
Euclides Mul&#233;mbw&#232;

Reputation: 1687

try:

SELECT
    row, original, ud.suspicious, isnull(u_transaction, '') as u_transaction, isnull(amount, '')
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;

not sure if it's going to work, no time to try, but i recall having a similar problem and solving like that.

Upvotes: 1

Related Questions