Where clause with IF condition
<p>I have a query which I'm having a little trouble getting to work as I want, the query looks like this: </p>

<pre><code>SELECT
  hf.file_path AS image,
  hop.order_product_name AS product,
  hop.order_product_price AS unit_flat_price,
  .... &lt;SHORTENED DOWN QUERY&gt; ....
  hc.currency_symbol AS currency,
  ho.order_payment_method AS payment_method,
  ho.order_created AS created
FROM
  shop_order AS ho
LEFT JOIN
  shop_order_product AS hop ON ho.order_id = hop.order_id
LEFT JOIN
  shop_product AS hp ON hop.product_id = hp.product_id
LEFT JOIN
  shop_file AS hf ON(
    IF(
      hp.product_parent_id &gt; 0,
      hp.product_parent_id,
      hp.product_id
    )
  ) = hf.file_ref_id
LEFT JOIN
  shop_currency AS hc ON ho.order_currency_id = hc.currency_id
WHERE
  ho.order_id = X AND(
    hf.file_ordering = 0 OR hf.file_ordering = NULL
  )
</code></pre>

<p>Now my issue is that sometimes the shop_file table won't have a match in the <code>shop_file</code>table and then I need my query to "ignore" that join, but if there IS a match, then I need it to apply the <code>AND</code> part in the <code>WHERE</code> clause I've tried</p>

<pre><code>WHERE
  ho.order_id = X (
    IF(
      hf.file_path = NULL, 
      '',
      AND(
        hf.file_ordering = 0 OR hf.file_ordering = NULL
      )
    )
  )
</code></pre>

<p>I tried with different values at the <code>hf.file_path =</code> line but nothing helped, I still got a syntax error <code>#1064 - You have an error in your .... syntax to use near '( IF(</code></p>

<p>I also tried</p>

<pre><code>WHERE
  ho.order_id = X AND(
    hf.file_id = NULL OR(
      hf.file_ordering = 0 OR hf.file_ordering = NULL
    )
  )
</code></pre>

<p>But that simply returns a empty result set (where if I remove the entire <code>AND( ... )</code> part I get my desired result.</p>

<p>Now I know I can do something along the lines of</p>

<pre><code>WHERE
  ho.order_id = X AND(
    IF EXIST(SELECT file_path FROM ... )
</code></pre>

<p>But that would require me to add the other <code>JOINS</code> in that query to get the right result (in case there is a image) and I want to try and avoid having to add the JOIN's in the <code>WHERE ... AND( IF_EXIST (SELECT</code> if possible.</p>