 <p><code>SELECT</code> using a correlated sub-query:</p> <pre><code>select t1.PROD_NO, t1.PROD_CAT (select max(t2.PROD_DESCRIPTION) from tablename t2 where t2.PROD_NO = t1.PROD_NO and t2.PROD_DESCRIPTION &lt;&gt; 'N/A' and t2.PROD_DESCRIPTION &lt;&gt; ''), from tablename t1 </code></pre> <p><code>JOIN</code> version, with a <code>GROUP BY</code> to find 'not in warehouse' or 'in warehouse':</p> <pre><code>select t1.PROD_NO, t1.PROD_CAT, t2.PROD_DESCRIPTION from tablename t1 LEFT JOIN (select PROD_NO, max(PROD_DESCRIPTION) from tablename where PROD_DESCRIPTION &lt;&gt; 'N/A' and PROD_DESCRIPTION &lt;&gt; '' group by PROD_NO) t2 ON t1.PROD_NO = t2.PROD_NO </code></pre> <p>If a prod_no has both 'in warehouse' and 'not in warehouse', the <code>MAX</code> will return 'not in warehouse' as value. If you want 'in warehouse' instead, switch to <code>min(t2.PROD_DESCRIPTION)</code> in the sub-select.</p>