phpmyadmin Question posted in
The official documentation can be found here.

How can i do a condition join with IF statement – Phpmyadmin

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")))
Tags:

2

Answers


  1. 0

    You can use case in where condition:

    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  (case when backup_orders.status_id =5 then
 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") end)

    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)

    Login or Signup to reply.
  2. 0

    If you examine the conditions in your edit, the date comparison never matters…

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

    If the status_id is 5, the first condition is True; 5 is NOT 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…

    AND
(
  backup_orders.status_id NOT IN (0,1,4,5,6,13,14) -- added 5 here
  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")
  )
)

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search