How would you do a inner join on a table where i need to filter the status, but on one of the status value, i need to perform an additional date comparison check on SQL phpmyadmin.
i have the following:
ex.
INNER JOIN orders backup_orders
ON backup_orders.bc_id = product_sold.order_id
AND backup_orders.status_id NOT IN (0, 1, 4, 6, 13, 14)
AND CONVERT_TZ( STR_TO_DATE( backup_orders.date_modified, "%a, %d %b %Y %T" ) , "GMT", "America/Chicago" )
>= CONVERT_TZ(STR_TO_DATE(:previous_week_start, "%a, %d %b %Y %T") , "GMT", "America/Chicago")
This only gets me the record where the order is cancelled and it was cancelled after the previous_week_start. I want all the records but if the status is 5 which is cancelled, perform the date comparison and if it was cancelled after the date then add it to the result.
#Edited:
AND(backup_orders.status_id NOT IN (0,1,4,6,13,14) OR (backup_orders.status_id = 5 AND CONVERT_TZ( STR_TO_DATE( backup_orders.date_modified, '%a, %d %b %Y %T' ) , 'GMT', 'America/Chicago' ) >= CONVERT_TZ(STR_TO_DATE("Thu, 11 Mar 2021 22:00:00 +0000", '%a, %d %b %Y %T') , "GMT", "America/Chicago")))
2
Answers
You can use case in where condition:
But wouldn’t it better to compare backup_orders.status_id=5 directly instead of backup_orders.status_id NOT IN (0, 1, 4, 6, 13, 14)
If you examine the conditions in your edit, the date comparison never matters…
If the
status_id
is 5, the first condition is True; 5 isNOT IN
that list.Then, it doesn’t matter what the 2nd or 3rd conditions are, the result of
TRUE OR (X AND Y)
is True, no matter what X or Y are.So, you need to add 5 to your list…
Now, a status of 5 can only resolve True if the date comparison Also resolves to True.
(Please forgive typos, I’m doing this on a phone.)