title: Self join without double result set?
body: <p>There are some duplicate (appearing twice or more) combinations of (id, name, dept_name). Note: the dept_name is nullable so there are often some nulls in that column.</p>
  
  <p>I am trying to get a result of the duplicate (id, name, dept_name) combos where one record has got a null mgr_id or sal but another record of that same duplicated combo has a non-null mgr_id or sal.</p>
  
  <p>This is what I have so far: </p>
  
  <pre><code> select t1.id, t1.name, t1.dept_name, t1.mgr_id, t2.mgr_id, t1.sal, t2.sal from employee t1
      inner join employee t2 on t2.id = t1.id and t2.name = t1.name
      where t1.id in
      (select id from 
      (select id, name, dept_name, count(*) from employee
      group by id, name, dept_name
      having count(*) &gt; 1))
      and (nvl(t1.mgr_id,0) &lt;&gt; nvl(t2.mgr_id,0) 
      or nvl(t1.sal,0) &lt;&gt; nvl(t2.sal,0)) 
      and t1.mgr_id is not null ;
  </code></pre>
  
  <p>If I don't leave the last line "and t1.mgr_id is not null" in the query I effectively receive the same resultset twice (which I don't want). However I need some way of incorporating the (t1.sal is not null) clause to not get the same resultset twice. </p>
  
  <p>Also, note there is no PK or sequence number column where I can just go:
  <code>t1.seq_no &lt;&gt; t2.seq_no</code>.</p>