Restricting results to only rows where one value appears only once <p>I have a query that is more complex than the example here, but which needs to only return the rows where a certain field doesn't appear more than once in the data set.</p> <pre><code>ACTIVITY_SK STUDY_ACTIVITY_SK 100 200 101 201 102 200 100 203 </code></pre> <p>In this example I don't want any records with an <code>ACTIVITY_SK</code> of 100 being returned because <code>ACTIVITY_SK</code> appears twice in the data set.</p> <p>The data is a mapping table, and is used in many joins, but multiple records like this imply data quality issues and so I need to simply remove them from the results, rather than cause a bad join elsewhere.</p> <pre><code>SELECT A.ACTIVITY_SK, A.STATUS, B.STUDY_ACTIVITY_SK, B.NAME, B.PROJECT FROM ACTIVITY A, PROJECT B WHERE A.ACTIVITY_SK = B.STUDY_ACTIVITY_SK </code></pre> <p>I had tried something like this:</p> <pre><code>SELECT A.ACTIVITY_SK, A.STATUS, B.STUDY_ACTIVITY_SK, B.NAME, B.PROJECT FROM ACTIVITY A, PROJECT B WHERE A.ACTIVITY_SK = B.STUDY_ACTIVITY_SK WHERE A.ACTIVITY_SK NOT IN ( SELECT A.ACTIVITY_SK, COUNT(*) FROM ACTIVITY A, PROJECT B WHERE A.ACTIVITY_SK = B.STUDY_ACTIVITY_SK GROUP BY A.ACTIVITY_SK HAVING COUNT(*) &gt; 1 ) </code></pre> <p>But there must be a less expensive way of doing this...</p>