title: SQL Server : compare two tables and return similar rows
body: <p>I want to compare two tables, source and target, and get similar rows.</p>
  
  <p>Compare source and target on <code>Id</code> one by one and:</p>
  
  <ul>
  <li>If matched and it's two or more on Target => select All matched from Target
  
  <ul>
  <li>If matched and it's two or more on Source => </li>
  <li>for first matched if it doesn't selected before
  
  <ul>
  <li>select Matched From target </li>
  </ul></li>
  <li>else (IF it have selected before)
  
  <ul>
  <li>check for next one matched </li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>I think need a recursive expression to check source and target one by one</p>
  
  <p>Source</p>
  
  <pre><code>x------x---------x
  |  Id  |   Name  |
  x------x---------x
  |   1  |   a     |
  |   2  |   b     |
  |   2  |   c     |
  |   3  |   d     |
  |   3  |   e     |
  |   4  |   x     |
  x------x---------x
  </code></pre>
  
  <p>Target</p>
  
  <pre><code>x------x---------x
  |  Id  |   Name  |
  x------x---------x
  |   1  |   f     |
  |   1  |   g     |
  |   2  |   h     |
  |   3  |   i     |
  |   3  |   j     |
  |   5  |   y     |
  x------x---------x
  </code></pre>
  
  <p>Result</p>
  
  <pre><code>x------x---------x
  |  Id  |   Name  |
  x------x---------x
  |   1  |   f     |
  |   1  |   g     |
  |   2  |   h     |
  |   3  |   i     |
  |   3  |   j     |
  x------x---------x
  </code></pre>
  
  <p>Test data</p>
  
  <pre><code>declare @s table(Id int, name varchar(20))
  DECLARE @t table( Id int, name varchar(20))
  
  INSERT @s values(1, 'a'), (2, 'b'), (2, 'c'), (3, 'd'), (3, 'e')
  
  INSERT @t values(1, 'f'), (1, 'g'), (2, 'h'), (3, 'i'), (3, 'j')
  </code></pre>