title: How to synthesize attribute for joined tables
body: <p>I have a view defined like this:</p>
  
  <pre><code>CREATE VIEW [dbo].[PossiblyMatchingContracts] AS
  SELECT 
      C.UniqueID,
      CC.UniqueID AS PossiblyMatchingContracts
  FROM  [dbo].AllContracts AS C
      INNER JOIN [dbo].AllContracts AS CC
          ON C.SecondaryMatchCodeFB = CC.SecondaryMatchCodeFB
              OR C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeLB
              OR C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeBB
              OR C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeBB
              OR C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeLB
  WHERE C.UniqueID NOT IN
      (
          SELECT UniqueID FROM [dbo].DefinitiveMatches
      )
      AND C.AssociatedUser IS NULL
      AND C.UniqueID &lt;&gt; CC.UniqueID
  </code></pre>
  
  <p>Which is basically finding contracts where f.e. the first name and the birthday are matching. This works great. Now I want to add a synthetic attribute to each row with the value from only one source row. </p>
  
  <p>Let me give you an example to make it clearer. Suppose I have the following table:</p>
  
  <pre><code>UniqueID  | FirstName | LastName  | Birthday
  
  1         | Peter     | Smith     | 1980-11-04
  2         | Peter     | Gray      | 1980-11-04
  3         | Peter     | Gray-Smith| 1980-11-04
  4         | Frank     | May       | 1985-06-09
  5         | Frank-Paul| May       | 1985-06-09
  6         | Gina      | Ericson   | 1950-11-04
  </code></pre>
  
  <p>The resulting view should look like this:</p>
  
  <pre><code>UniqueID | PossiblyMatchingContracts | SyntheticID
  
  1        | 2                         | PeterSmith1980-11-04
  1        | 3                         | PeterSmith1980-11-04
  2        | 1                         | PeterSmith1980-11-04
  2        | 3                         | PeterSmith1980-11-04
  3        | 1                         | PeterSmith1980-11-04
  3        | 2                         | PeterSmith1980-11-04
  4        | 5                         | FrankMay1985-06-09
  5        | 4                         | FrankMay1985-06-09
  6        | NULL                      | NULL [or] GinaEricson1950-11-04
  </code></pre>
  
  <p>Notice that the SyntheticID column uses ONLY values from one of the matching source rows. It doesn't matter which one. I am exporting this view to another application and need to be able to identify each "match group" afterwards. </p>
  
  <p>Is it clear what I mean? Any ideas how this could be done in sql?</p>
  
  <p>Maybe it helps to elaborate a bit on the actual use case:</p>
  
  <p>I am importing contracts from different systems. To account for the possibility of typos or people that have married but the last name was only updated in one system, I need to find so called 'possible matches'. Two or more contracts are considered a possible match if they contain the same birthday plus the same first, last or birth name. That implies, that if contract A matches contract B, contract B also matches contract A.</p>
  
  <p>The target system uses multivalue reference attributes to store these relationships. The ultimate goal is to create user objects for these contracts. The catch first is, that the shall only be one user object for multiple matching contracts. Thus I'm creating these matches in the view. The second catch is, that the creation of user objects happens by workflows, which run parallel for each contract. To avoid creating multiple user objects for matching contracts, each workflow needs to check, if there is already a matching user object or another workflow, which is about to create said user object. Because the workflow engine is extremely slow compared to sql, the workflows should not repeat the whole matching test. So the idea is, to let the workflow check only for the 'syntheticID'. </p>