Need help with a MS-SQL query

What I am trying to accomplish is to get the ID column from @temp2 to correspond to both intA and intB columns from @temp1 The end result output I would hope to see would look something like:

intA    intB    'asset1'    'asset2'    'name1' 'name2'

1   1   108 108 Cash    Cash

1   2   108 109 Cash    Commodities

1   3   108 138 Cash    Stock
.
.
.

2   5   109 111 Commodities Equity

Here is some sample data I am working with:

declare @temp1 table
(
    intA    int,
    intB    int
)
insert @temp1
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union all
select 2,1 union all
select开发者_如何学运维 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5

select * from @temp1
declare @temp2 table
(
    oneup   int,
    id  int,
    name    varchar(30)
)
insert @temp2
select 1,108,'Cash' union all
select 2,109,'Commodities' union all
select 3,138,'Stock' union all
select 4,110,'Bonds' union all
select 5,111,'Equity'

select * from @temp2

select t1.*,t2.* from @temp1 t1
inner join @temp2 t2
on t1.intA = t2.oneup

I can't get the join to work right to give me an output like I expect. Using SQL2008

thanks for any assistance!

You need to join to @temp2 twice, once for intA and once for intB.

select t1.intA, t1.intB, 
       t2a.id as asset1,  t2b.id as asset2, 
       t2a.name as name1, t2b.name as name2
    from @temp1 t1
        inner join @temp2 t2a
            on t1.intA = t2a.oneup
        inner join @temp2 t2b
            on t1.intB = t2b.oneup