请教一个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