Search code examples
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