请教一个sql~~
tbBianMa:
Nam TableNam
CKD tbChuKuDan
DBD tbDiaoBoDan

tbShenPi:
ID
CKD001
DBD001

tbChuKuDan:
ID State
CKD001 通过


tbDiaoBoDan:
ID State
DBD001 未通过

我要根据tbShenPi得到:
ID State
CKD001 通过
DBD001 未通过


[解决办法]
select tbShenPi.ID,t.State
from tbShenPi
inner join (select ID,State from tbChuKuDan union all select ID,State from tbDiaoBoDan)t on tbShenPi.ID=t.ID

[解决办法]
--TRY
select a.ID,b.State from tbShenPi a
left join (select * from tbDiaoBoDan union all select * from tbChuKuDan ) b
on a.ID=b.ID
[解决办法]
select a.ID,state = (case when charindex( 'CKD ',ID)> 0 then b.state else c.state end ) from tbShenPi a left join tbChuKuDan b on a.ID = b.ID left join tbDiaoBoDan c on a.ID = c.ID
[解决办法]
select s.ID, State = case left(s.ID) when 'CKD ' then c.State
when 'DBD ' then d.State end
from tbShenPi s, tbChuKuDan c, tbDiaoBoDan, d
where s.ID = c.ID and s.ID = d.ID