title: select rows where criteria appears more than once sql
body: <p>I have a view on MySQL, and I want to filter out those rows where temID appears more than once for a particular user.</p>
  
  <p>Let's say a sample of the data is:</p>
  
  <pre><code>
  id  name    ques    temID
  
  55  peter   1993    680
  55  peter   1994    680
  55  peter   1476    680
  55  peter   1879    684
  55  peter   1440    686
  55  peter   1440    686
  55  peter   1438    686
  55  peter   1921    687
  55  peter   1922    687
  55  peter   1921    687
  56  john    1853    684
  56  john    1853    684
  56  john    1539    687
  56  john    1998    1512
  56  john    2281    1534
  56  john    2282    1534
  56  john    2282    1534
  </code></pre>
  
  <p>I want the results to be</p>
  
  <pre><code>
  id  name    ques    temID
  
  55  peter   1993    680
  55  peter   1994    680
  55  peter   1476    680
  55  peter   1440    686
  55  peter   1440    686
  55  peter   1438    686
  55  peter   1921    687
  55  peter   1922    687
  55  peter   1921    687
  56  john    1853    684
  56  john    1853    684
  56  john    2281    1534
  56  john    2282    1534
  56  john    2282    1534
  </code></pre>
  
  <p>These three rows should be removed because the temID only appears once for each user:</p>
  
  <pre><code>
  id  name    ques    temID
  
  55  peter   1879    684
  56  john    1539    687
  56  john    1998    1512
  </code></pre>
  
  <p>I tried </p>
  
  <pre><code>select * from v_tem 
  WHERE temID IN
      (     SELECT temID
            FROM v_tem 
            GROUP BY id, temID
            HAVING COUNT(*) > 1
      )
  GROUP BY id, temID
  ORDER BY id, temID;
  </code></pre>
  
  <p>but the results are</p>
  
  <pre><code>
  id  name    ques    temID
  
  55  peter   1993    680
  55  peter   1994    680
  55  peter   1476    680
  55  peter   1879    684
  55  peter   1440    686
  55  peter   1440    686
  55  peter   1438    686
  55  peter   1921    687
  55  peter   1922    687
  55  peter   1921    687
  56  john    1853    684
  56  john    1853    684
  56  john    1539    687
  56  john    2281    1534
  56  john    2282    1534
  56  john    2282    1534
  </code></pre>
  
  <p>Only </p>
  
  <pre><code>
  id  name    ques    temID
  
  56  john    1998    1512
  </code></pre>
  
  <p>is removed.</p>
  
  <p>My guess is that because temID 684 appears more than once for john, so it passes the where clause even though it appears only once for peter. Same goes for temID 687. Even though it only appears once for john, because it appears twice for peter, it is not omitted by the where clause.</p>
  
  <p>Please help!</p>