如何从表格1中搜索出连接的表格2中的列都属于一类的行。

[techie]

我是一名小白。用的是SQLserver2008版，表格如下

textTable(id, text, user_id) 任务表
userTable(user_id, user_name, department_id, role) 员工表
departmentTable(department_id, department_name) 部门表

我要select同一个部门的人员的任务。
这个select应该怎么写？

[锟斤拷锟斤拷]

SELECT A.* FROM textTable A,userTable B,departmentTable C WHERE A.user_id = B.user_id AND B.department_id = C.department_id AND C.department_name = '某某部'

[morliz子轩]

引用 楼主 techie 的回复:

我是一名小白。用的是SQLserver2008版，表格如下 textTable(id, text, user_id) 任务表 userTable(user_id, user_name, department_id, role) 员工表 departmentTable(department_id, department_name) 部门表 我要select同一个部门的人员的任务。 这个select应该怎么写？

declare @departna varchar(50) ='部门名称'

select a.id
    ,c.department_name
    ,b.user_name
    ,a.text

from textTable a
inner join userTable b on a.user_id = b.user_id
inner join departmentTable c on b.department_id = c.department_id
where c.department_name = @departna

[zemkey]

通过子查询使用两个内连接多表查询， select textTable.text,tempTable.user_name,tempTable.department_name from textTable inner join (select user_id,user_name,departmentTable.department_name department_name from userTable inner join departmentTable on userTable.department_id =departmentTable.department_id) tempTable on tempTable.user_id=textTable.user_id

[jakefj]

select a.*,b.*,c.* from 部门表 a left join 员工表 b on a.department_id=b.department_id left join 任务表 c on b.user_id=c.user_id where a.department_id=你要查的部门