MySQL select as special and the output exclude null version <p>This is my sql statement, as you can see the the select SELECT category as special, how can I only get special is NOT NULL.</p> <pre><code>SELECT p.product_id, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start &lt; NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end &gt; NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) WHERE AND p.status = '1' AND p.date_available &lt;= NOW() AND p2c.category_id = '20' GROUP BY p.product_id ORDER BY p.sort_order ASC LIMIT 0,5 </code></pre> <p>With above statement, I have the following output: <img src="https://i.stack.imgur.com/3MDsz.png" alt="enter image description here"></p> <p>I tried add the WHERE after the date_available, then I got no result out. I try to use <code>special</code> IS NOT NULL it showed: Unknown column 'special' in 'where clause'</p> <p>Anyone can help in this matter? Thanks!</p>