mysqlmysql-error-1064

How to correct the syntax error in the below query?


LEFT OUTER JOIN rule sod  ON ( 
    (
      xd.accou_id = sod.id 
      where  xd.item_type = 28
    ) 
    OR (
      pa.transaction_id = sod.id 
      AND pa.transaction_type = 28
    )
  ) 

my Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where xd.item_type = 28 ) OR ( pa.transaction_id = sod.i' at line 147


Solution

  • you can't use WHERe, but you can use A CASE WHEN clause

    Like

    SELECT 
        *
    FROM
        xd
            INNER JOIN
        pd ON xd.id = pd.id
            LEFT OUTER JOIN
        rule sod ON sod.id = CASE
            WHEN xd.item_type = 28 THEN xd.accou_id
            WHEN pa.transaction_type = 28 THEN pa.transaction_id
        END
    

    sample fidle