title: Join 2 tables with match of 2 columns from two tables
body: <p>I have 2 tables :</p>
  <pre><code>TransferPointType
  --------------------------------------------------
  [Player(int)] [ID(int)] [Type(tinyint)] [Level(int)] [Before(money)] [Now(money)] [After(money)]
  X 
  X
  X
  
  [Transfer]
  --------------------------------------------------
  [Player(int)] [ID(int)] [Type(tinyint)] [Amount(money)] [Created(datetime)] [Description(nvarchar256)]
  </code></pre>
  <p>The resulting table should look like this</p>
  <pre><code>        [Player] [ID]                 [Level] [Before] [Now] [After] [Created] [Description]
  --[Transfer] = TransferPointType--
  
  </code></pre>
  <p>and conditions</p>
  <pre><code>match between TransferPointType.player=[Transfer].player, TransferPointType.ID=[Transfer].ID
  according to this match add others columns [Type] [Level] [Before] [Now] [After] from TransferPointType
  and columns - [Created] [Description] from [Transfer]
  -----------------------------------------------------------------------
  where player=1111 and ID IN (1111,xxxx,xxx,xx,xx,xxx)
  </code></pre>
  <p>I think about the left join or union but I am not able to write a reasonable JOIN. If I tried UNION where  i optimalized number of columns, I got to the point where the sql server show an error: Arithmetic overflow error -datetime. So, I tried to convert it with (example)  <code>select CONVERT(datetime, cast(20140806 as char(8)) )</code> without success either.</p>
  <p>Maybe I'm thinking complicatedly and it could be written by simply select ...</p>