 <p>You could use the WITH clause to achieve the same effect:</p> <pre><code>WITH DISTINCT_TALENTS(PERSONID, TALENT) AS (SELECT DISTINCT PERSONID, TALENT FROM TALENTS) SELECT DISTINCT PERSONID, TALENT FROM (SELECT A.PERSONID, CASE WHEN TALENT_COUNT = 2 THEN 'BOTH' ELSE A.TALENT END FROM DISTINCT_TALENTS A INNER JOIN (SELECT PERSONID, COUNT(TALENT) TALENT_COUNT FROM DISTINCT_TALENTS GROUP BY PERSONID) B ON A.PERSONID = B.PERSONID) </code></pre> <p>First you create a virtual DISTINCT_TABLES table:</p> <pre><code>+------------------+ | personid talent | +------------------+ | 1 play | | 1 swim | | 2 play | | 3 swim | +------------------+ </code></pre> <p>next you create a subquery b with the following</p> <pre><code>+------------------------+ | personid talent_count | +------------------------+ | 1 2 | | 2 1 | | 3 1 | +------------------------+ </code></pre> <p>you join with original DISTINCT_TALENTS to obtain</p> <pre><code>+----------+--------+--------------+ | personid | talent | talent_count | +----------+--------+--------------+ | 1 | both | 2 | | 1 | both | 2 | | 2 | play | 1 | | 3 | swim | 1 | +----------+--------+--------------+ </code></pre> <p>you take the distinct personid, talent to obtain the final result.</p> <p>A solution similar to using exists is:</p> <pre><code>SELECT DISTINCT PERSONID, TALENT FROM ( SELECT B.PERSONID, CASE WHEN A.TALENT IS NULL THEN 'swim' WHEN B.TALENT IS NULL THE 'play' ELSE 'both' END TALENT FROM TALENTS A FULL OUTER JOIN TALENTS B ON A.PERSONID = B.PERSONID AND A.TALENT='play' AND B.TALENT='swim' ) </code></pre> <p>And finally, also with the <code>EXISTS</code> function used like a lookup function:</p> <pre><code>SELECT DISTINCT PERSONID, TALENT FROM ( SELECT A.PERSONID, CASE WHEN A.TALENT = 'play' AND EXISTS (SELECT 1 FROM TALENTS B WHERE A.PERSONID = B.PERSONID AND B.TALENT = 'swim') THEN 'both' WHEN A.TALENT = 'swim' AND EXISTS (SELECT 1 FROM TALENTS B WHERE A.PERSONID = B.PERSONID AND B.TALENT = 'play') THEN 'both' ELSE A.TALENT END TALENT FROM TALENTS A) </code></pre>