求一多表查询SQL

[Fengxiubing2000]

a表
ID shopname
20100901 分店1

b表
ID design_no manager_no
20100901 00786 00787

c表
_no _name
00786 何小艺
00787 何管

要求结果显示：
ID shopname design_name manager_name
20100901 分店1 何小艺 何管

[百年树人]

select b.id,a.shopname,c._name as design_name,d._name as manager_name
from b
join a on a.id=b.id
left join c on b.design_no=c._no
left join c as d on b.manager_no=d._no

[百年树人]

---测试数据---
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[shopname] varchar(5))
insert [a]
select '20100901','分店1'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[design_no] varchar(5),[manager_no] varchar(5))
insert [b]
select '20100901','00786','00787'
go
if object_id('[c]') is not null drop table [c]
go
create table [c]([_no] varchar(5),[_name] varchar(6))
insert [c]
select '00786','何小艺' union all
select '00787','何管'
go
 
---查询---
select b.id,a.shopname,c._name as design_name,d._name as manager_name
from b  
join a on a.id=b.id
left join c on b.design_no=c._no
left join c as d on b.manager_no=d._no

---结果---
id          shopname design_name manager_name
----------- -------- ----------- ------------
20100901    分店1      何小艺         何管

(1 行受影响)

[ForFumm]

select b.id,a.shopname,b._name,c._name design_name,d._name manager_name from b表 b left join a表 a on b.ID=a.ID
left join c表 c on b.design_no=c._no left join c表 d on b.manager_no=d._no

[Fengxiubing2000]

谢谢楼上！
数据库是access
运行时出错！

[黄_瓜]

--access 的语法好像是这样的
select b.id,a.shopname,c._name as design_name,d._name as manager_name
from b  
(inner join a on a.id=b.id)
(left join c on b.design_no=c._no)
left join c as d on b.manager_no=d._no

[Fengxiubing2000]

仍然提示 "from 子句语法错误"

[竹林深处]

ms_sql server 中正常

[百年树人]

[Quote=引用 6 楼 fengxiubing2000 的回复:]
仍然提示 "from 子句语法错误"
[/Quote]

把你的语句贴出来

[xiaoxiangqing]

select a.*,c._name design_name,d._name manager_name from a join b on a.ID=b.ID
join c on b.design_no=c._no join c d on b.nanager_no=d._no

[lg200015017]

join c d on b.nanager_no=d._no
join d on b.nanager_no=d._no

[lg200015017]

select a.*,c._name design_name,d._name manager_name from a ，b，c,c d where
 a.ID=b.ID
  and b.design_no=c._no 
and b.nanager_no=d._no