一个SQL查询语句，，帮个忙！

[evilks]

有四张表，empdept(empid,deptid,posiid),employee(empid,empname),dept(deptid,deptname),posi(posiid,posiname)
怎么样查出empid,empname,posiname,deptname.
谢谢！！

[zjcxc]

--你想得到什么样的结果?

declare @empdept table(empid int,deptid int,posiid int)
insert into @empdept
select 0,1,1

declare @employee table(empid int,empname varchar(10))
insert into @employee
select 1,'张三'

declare @dept table (deptid int,deptname varchar(10))
insert into @dept
select 0,'经营部'

declare @posi table(posiid int,posiname varchar(10))
insert into @posi
select 1,'部长'

select a.empid
,b.empname
,c.posiname
,d.deptname
from @empdept a
full join @employee b on a.empid=b.empid
full join @posi c on a.posiid=c.posiid
full join @dept d on a.deptid=d.deptid


/*--结果
empid empname posiname deptname
----------- ---------- ---------- ----------
NULL 张三 NULL NULL
0 NULL 部长 NULL
NULL NULL NULL 经营部

（所影响的行数为 3 行）

--*/

[evilks]

谢谢大家了，我把表empdept去掉了，在employee中加了两个字段。。

[nboys]

有四张表，empdept(empid,deptid,posiid),employee(empid,empname),dept(deptid,deptname),posi(posiid,posiname)
怎么样查出empid,empname,posiname,deptname.

-----------------------
select
empid,empname,posiname,deptname
from empdept a
join employee b on a.empid=b.empid
join dept c on a.deptid=c.deptid
join posi d on a.posiid=d.posiid

[lightkkk]

UP

[pengdali]

select a.empid,b.empname,d.posiname,c.deptname from empdept a join employee b on a.empid=b.empid
join dept c on a.deptid=c.deptid join posi d on a.posiid=d.posiid

[evilks]

查不到数据，，比如说：
a(0,1,1),b(1,张三),c(0,经营部),d(1,部长)...

[yujohny]

select A.empid,A.empname,D.posiname,C.deptname
from employee A inner join empdept B on B.empid=A.empid
inner join dept C on deptid=B.deptid
inner join posi D on posiid=B.posiid

[yujohny]

select empid,(select empname from employee where empid= A.empid),
(select posiname from posi where posiid=A.posiid),
(select deptname from dept where deptid=A.deptid)
from empdept A

[yujohny]

select A.empid,A.empname,D.posiname,C.deptname
from empdept A,employee B,dept C,posi D
where B.empid=A.empid and C.deptid=A.deptid and D.posiid=A.posiid
或者
select B.empid,A.empname,D.posiname,C.deptname
from empdept B inner join employee A on B.empid=A.empid
inner join dept C on deptid=B.deptid
inner join posi D on posiid=B.posiid

[pengdali]

select a.empid,b.empname,d.posiname,c.deptname from empdept a left join employee b on a.empid=b.empid
left join dept c on a.deptid=c.deptid left join posi d on a.posiid=d.posiid

[evilks]

只能查出前两个字段的值，后面的就NULL,我再看看！谢

[zjcxc]

select a.empid
,b.empname
,c.posiname
,d.deptname
from empdept a
full join employee b on a.empid=b.empid
full join posi c on a.posiid=c.posiid
full join dept d on a.deptid=d.deptid

[evilks]

楼上，你的答案有问题。