sql语句？

[Ice_wang]

firsttable:
ID ClientName ClientType ClientAddress
1 w 1 1
2 h 3 1
3 a 3 2
4 g 4 4
secondTable:
ID code text
A01 1 大客户
A01 2 一般
A01 3 差
A01 4 非常差
A02 1 北京
A02 2 上海
A02 3 广州
A02 4 深圳

求：
ID ClientName Clientype(对应的text值) clientAddress(对应的text值)
谢谢。。。急。。。

[子陌红尘]

select
ID,
ClientName,
(select [text] from secondTable where code=f.Clientype and ID='A01') as Clientype,
(select [text] from secondTable where code=f.ClientAddress and ID='A02') as ClientAddress,
from
firsttable f

[子陌红尘]

select
ID,
ClientName,
(select [text] from secondTable where code=f.Clientype and ID='A01') as Clientype,
(select [text] from secondTable where code=f.ClientAddress and ID='A02') as ClientAddress
from
firsttable f

[marco08]

create table firsttable(ID int, ClientName char(1), ClientType int, ClientAddress int)
insert firsttable select
1, 'w', 1, 1
union all select 2, 'h', 3, 1
union all select 3, 'a', 3, 2
union all select 4, 'g', 4, 4

create table secondTable(ID char(3), code int, [text] nvarchar(10))
insert secondTable select 'A01', 1, '大客户'
union all select 'A01', 2, '一般'
union all select 'A01', 3, '差'
union all select 'A01', 4, '非常差'
union all select 'A02', 1, '北京'
union all select 'A02', 2, '上海'
union all select 'A02', 3, '广州'
union all select 'A02', 4, '深圳'

select firstTable.ID, ClientName,
ClientType, ClientType_Text=(select [text] from secondTable where code=ClientType and ID='A01'),
ClientAddress, ClientAddress_Text=(select [text] from secondTable where code=ClientAddress and ID='A02')
from firstTable

--result
ID ClientName ClientType ClientType_Text ClientAddress ClientAddress_Text
----------- ---------- ----------- --------------- ------------- ------------------
1 w 1 大客户 1 北京
2 h 3 差 1 北京
3 a 3 差 2 上海
4 g 4 非常差 4 深圳

(4 row(s) affected)