title: how to write this type of inner join
body: <p>I have following table <code>tbl_info</code></p>
  
  <pre><code>  ---------------------------------------
    |  id   |  parentid  |  sortsequence  |
    |--------------------------------------
    | 1     |   0        |        1       | 
    |--------------------------------------
    | 2     |   1        |        1       | 
    |--------------------------------------
    | 3     |   1        |        1       | 
    |--------------------------------------
    | 4     |   1        |        2       |
    |--------------------------------------
    | 5     |   0        |        1       | 
    |--------------------------------------
    | 6     |   5        |        1       | 
    |-------------------------------------|
    | 7     |   5        |        2       |
  </code></pre>
  
  <p><strong>My Problem</strong></p>
  
  <p>I need to find out ids of those records and their children ids (where id of some record exists as parentid) whose child's <code>sortsequence</code> column is not unique. For Example If <code>id 1</code> has children <code>2,3,4</code> then sortsequence for <code>2,3,4</code> must be unique. If they are not unique, they are faulty records and I need to find out such records. </p>
  
  <p><strong>updated after comments</strong></p>
  
  <p>Parent child relationship is made from <code>id</code> and <code>parentid</code> fields. If <code>id</code> of some record exists as <code>parentid</code> of other records then these other records are children of those records. <strong>Now I need to find out those records where <code>parentid</code> is same but sortsequence is not unique.</strong>  The ideal condition and correct condition is that all records where parentid is same then sortsequence must be unqiue. In above case records <code>2,3,4</code> are faulty records. I need to find out these records</p>
  
  <p><strong>What I have done so far</strong></p>
  
  <p>I have searched a lot and written this query</p>
  
  <pre><code>SELECT id, parentid, tbl_info.sortsequence FROM tbl_info
     INNER JOIN (SELECT sortsequence FROM tbl_info
  GROUP BY sortsequence HAVING COUNT(id) &gt; 1) dup ON tbl_info.sortsequence = dup.sortsequence
  </code></pre>
  
  <p>This query first finds all records whose sortsequence is the same but I need to find out with respect to <code>parentid</code>. Second I have million of records in this table so I need a query with minimal processing time.</p>