title: How to find non-matching nth child rows by name?
body: <p>I am trying to write a query to find the nth child records only where the nth child records do not match in name.</p>
  
  <p>Simplified Data Structure:</p>
  
  <blockquote>
    <ul>
    <li>Root (PK: id)
    
    <ul>
    <li>Job (PK: id, FK: idRoot -> Root.id)  
    
    <ul>
    <li>Task (PK: id, FK: idParent -> Job.id, FK: idRoot -> Root.id) </li>
    </ul></li>
    </ul></li>
    </ul>
    
    <p>All ID fields are strings (I'm at the mercy of the current design, it's not ours to change)</p>
  </blockquote>
  
  <p>The idea is that a sub-query will get only one match per root, and those will be evaluated against the other single match from the other roots.  Unless all of the results from the subquery match (on a specified row), they all fail.  Basically I need all nth tasks to have the same name across all of the selected roots, otherwise they all fail. </p>
  
  <p>I have a query that gives me each nth child record per root (where Task is the child table).  However I can't figure out how to check for equality. Additionally this is required to work for both SQL Compact and SQL Server, which limits some functions.</p>
  
  <p>This is for n = 1 (first task)</p>
  
  <pre><code>SELECT COUNT(r.id) as RowIndex, t.id, t.idRoot, t.Name
  FROM Task t
    INNER JOIN Job j ON j.id = t.idParent
    INNER JOIN (
      SELECT b.id, b.idParent, b.idRoot, b.StartDate
      FROM Task b
        INNER JOIN Job c ON b.idParent = c.idRec
    ) r ON t.StartDate &gt;= r.StartDate
        AND r.idParent = j.id 
        AND t.idRoot = r.idRoot
  WHERE t.idRoot IN ('1', '2', '3', '4')
    AND j.Type LIKE '%Example%'
  GROUP BY t.idRoot, t.id, t.Name
  HAVING COUNT(r.id) = 1    
  </code></pre>
  
  <p>That gives me back something like:</p>
  
  <pre><code>╔══════════╦════╦════════╦════════════╗
  ║ RowIndex ║ ID ║ IDRoot ║    Name    ║
  ╠══════════╬════╬════════╬════════════╣
  ║        1 ║  4 ║      2 ║ 1st Task   ║
  ║        1 ║  7 ║      3 ║ 1st Task   ║
  ║        1 ║ 11 ║      4 ║ First Task ║
  ╚══════════╩════╩════════╩════════════╝
  </code></pre>
  
  <p>So in the above results I'm correctly getting back the first task from 'Example' job types.  I can't figure out how to get results only when the Names didn't all match.  If each of the names was '1st Task', the query should return back nothing.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p><a href="https://pastebin.com/LSRX85sg" rel="nofollow noreferrer">Here's a link</a> to pastebin that can create the tables and load sample data. </p>
  
  <p><em>Query A</em> returns the first task for job type A, and the results show the task names don't match.  Therefore I'd want both of these task IDs to be returned.</p>
  
  <p><em>Query B</em> returns the first tasks for job type B, and the results show all task names match.  Therefore I'd want none of these task IDs returned. </p>