Tricky join question 1

[lechuck]

I have two tables usrTable and testTable.

select * from usrTable

u_id usr
----------- ------------
1 sa
2 bob
3 john

select * from testTable

t_id usr1 usr2 usr3
---------- ---------- ---------- ----------
1 1 1 2
2 1 2 2
3 2 3 3
4 1 2 3

Now I want to join this tables together so that instead of showing the id of each usr it will show the name...so the result I'm looking for is:
t_id usr1 usr2 usr3
----------- ----------- ----------- -----------
1 sa sa bob
2 sa bob bob
3 bob john john
4 sa bob john

But I don't know how to write a query that givs me that result.
If it only whare one usr col in testTable then a simple Inner join like this whould work:

select testTable.t_id, usrTable.usr from testTable inner join usrTable on testTable.usr1 = usrTable.u_id

t_id usr
------ -------
1 sa
2 sa
3 bob
4 sa

I have tested diffrent kinds of joins and the UNION operator but I don't manage to get the result I'm looking for. Please help me.

 

[swampBoogie]

select testTable.t_id, u1.usr as usr1, 
       u2.usr as usr2, u3.usr as usr3
from testTable inner join usrTable as u1 
on testTable.usr1 = u1.u_id
inner join usrTable as u2 
on testTable.usr2 = u2.u_id
inner join usrTable as u3
on testTable.usr3 = u3.u_id

 

[JamesLean]

SELECT t.t_id,
  u1.usr AS usr1,
  u2.usr AS usr3,
  u3.usr AS usr3
FROM testtable t
  JOIN usrtable u1 ON t.usr1 = u1.u_id
  JOIN usrtable u2 ON t.usr2 = u2.u_id
  JOIN usrtable u3 ON t.usr3 = u3.u_id
ORDER BY t.t_id

--James

 

[hmckillop]

SELECT tt.u_id, us1.usr, us2.usr, us3.usr
FROM testTable tt
INNER jOIN UsrTable us1 on tt.usr1 = us1.u_id
INNER jOIN UsrTable us2 on tt.usr1 = us2.u_id
INNER jOIN UsrTable us3 on tt.usr1 = us3.u_id

 

[hmckillop]

Pipped to the post!

 

[lechuck]

Thank you very much!