title: SQL: How to keep track rows already matched in a correlated subquery?
body: <p>Here's another challenge for you SQL-gurus out there...</p>
  
  <p>I wrote another question here on matching two result sets(<a href="https://stackoverflow.com/questions/1421842/how-to-match-compare-values-in-two-resultsets-in-sql-server-2008">How to match/compare values in two resultsets in SQL Server 2008?</a>). This question is a sequel to that question and answer, but since its a different topic I'm creating it as a new question.</p>
  
  <p>Quassnoi suggests the following solution to selecting all the users that meet the skills required by a given project:</p>
  
  <pre><code>SELECT * 
  FROM   Users u  
  WHERE  NOT EXISTS 
   ( 
   SELECT  NULL  
   FROM    ProjectSkill ps        
   WHERE   ps.pk_project = @someid               
   AND NOT EXISTS  
    (                
    SELECT  NULL                
    FROM    UserSkills us                
    WHERE   us.fk_user = u.id                        
            AND us.fk_skill = ps.fk_skill               
     )        
   )
  </code></pre>
  
  <p>This works great. But what if I wanted to create a single query, that lists all the projects of a specific day including their best matching user. I imagine the following format:</p>
  
  <pre><code>projectid        userid
  ----------       -----------
  1                1234
  2                5678
  3                4321
  4                8765
  </code></pre>
  
  <p>It is important, that a user is only suggested once in the list! ... because the projects are on the same day and Users must not be double-booked.</p>
  
  <p>Now, I can imagine some SQL like:</p>
  
  <pre><code>SELECT p.id 'projectid', ( SELECT TOP 1 u.id 
          FROM   Users u  
          WHERE  NOT EXISTS 
          ( 
               SELECT  NULL  
               FROM    ProjectSkill ps        
               WHERE   ps.pk_project = p.id 
               AND NOT EXISTS  
                   (                
            SELECT  NULL                
            FROM    UserSkills us                
            WHERE   us.fk_user = u.id                        
                   AND us.fk_skill = ps.fk_skill               
           )        
           )
           ORDER BY rating DESC
        ) 'userid'
  FROM Projects p 
  WHERE startdate &gt; @beginningofday 
        AND startdate &lt; @endofday
  </code></pre>
  
  <p>But this (obviously) happily suggests the same user again and again, as long as he has the required skills for the projects. </p>
  
  <p>Does anybody have a suggestion on how to keep track of which rows in the Users table are already matches earlier in the query? A use of a variable maybe? Or is there another smart way around this that I'm missing?</p>
  
  <p>The query must run on SQL Server 2008. </p>
  
  <p>Any help will be greatly appreciated! Thanks.</p>
  
  <p>Regards
  Alex</p>