Left join tables with multiple conditions
I am struggling with one request. I need LEFT JOIN
two tables where the specific id is the same and where the dates are different:
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 me the correct number of rows, but I also need to check if the transaction date is greater than the suspicious date from the first table. As soon as I add this condition (tried in the same place and on startup), it excludes all fields where there is no transaction date.
Meaning, if I have LEFT JOIN
these 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 need to eliminate the amount and date for 01.01.2000
, as it is older than the suspicious date and I do not need the additional amount, but I do need the original. 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 this 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 deleting records with help LEFT JOIN
and how can I fix it to get the data I need?
Edit: Complete 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 )
Since this is too messy, I am trying to get rid of the two sub queries and join them to get the sum SUM( external_account )
. Maybe this will help you understand my problem.
source to share
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 will work, not time to give it a try, but I remember that I had a similar problem and solved a similar one.
source to share
I think it gives NULL in u_transaction and quantity, because you are using AND with ON condition, try putting it in where where and see what happens.
try it
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;
source to share
If you need a NULL string in the output, add an IF statement to 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 request. It will be very helpful if you can create table structures in sqlfiddle.com if this query doesn't 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
source to share
You cannot expect an OR clause with an OUTER join to return correct results. Since you have NULL in the comparison column, it will never return "true" for any comparison, as that technically means "no value". For your case, you may need to replace NULL with a value that is always true before the 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))
source to share