select a.name,isnull(b.chk1,''),isnull(b.chk2,''),isnull(d.chk3,''),isnull(d.chk4 ,'')from 病人表 a left join 病人檢查單表 b on a.id =b.sickerid
left join 檢查項目表1 c on b.checkid=c.checkid
left join 檢查項目表2 d on b.checkid=d.checkid
left join 檢查項目表1 c on b.checkid=c.checkid
left join 檢查項目表2 d on b.checkid=d.checkid
from 病人表 a,病人检查单表 b
left join 检查项目表1 c on b.checkid=c.checkid
left join 检查项目表2 d on b.checkid=d.checkid
where a.id=b.sickerid
select t2.name 姓名,t3.chk1 检查1,t3.chk2 检查2,t4.chk3 检查3,t4.chk4 检查4
from 病人检查单表 t1
left join 病人表 t2 on t2.id =t1.sickerid
left join 檢查項目表1 t3 on t2.checkid=t3.checkid
left join 檢查項目表2 t4 on t2.checkid=t4.checkid