Join 2 tables with match of 2 columns from two tables

Question

I have 2 tables :

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)]

The resulting table should look like this

        [Player] [ID]                 [Level] [Before] [Now] [After] [Created] [Description]
--[Transfer] = TransferPointType--

and conditions

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)

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) select CONVERT(datetime, cast(20140806 as char(8)) ) without success either.

Maybe I'm thinking complicatedly and it could be written by simply select ...

Answer 1

Try this:

select a.type, a.level, a.before, a.now, a.after, b.created, b.description
from TransferPointType a
join Transfer b on a.player = b.player and a.ID = b.ID
where a.player = 1111
and a.ID IN (1111,xxxx,xxx,xx,xx,xxx);

If you expect TransferPointType has ID's that are not in Transfer table, then add "left" in front of join statement.