用一条SQL语句查询2/3的表

Question

我有三个表：users, projects, user_project

user_project包含user_id, project_id和role_id，其中user_id和project_id都是连接到projects.project_id和users.user_id的主键。

我想通过user_project从projects表中获取与某个项目相关的users表中所有用户的姓名。

我必须使用什么SQL语句？我是这样想的：

SELECT 
    users.name, users.surname 
FROM users 
WHERE users.user_id = user_project.user_id AND projects.project_id = @parameter

我使用的是SQL Server 2012和ASP.NET/VB.NET。

Db图：

​

​

http://i.imgur.com/lrv52wO.png

Answer 1

如果您想要基于Projects表中的内容进行选择，则需要将连接到其他表-从Users到User_Project一直到Projects：

SELECT 
    u.name, u.surname 
FROM users u
INNER JOIN user_project up ON u.user_id = up.user_id
INNER JOIN project p ON p.project_id = up.project_id
WHERE p.project_name = 'something'

或者至少链接到链接表，如果可以使用project_id作为条件的话：

SELECT 
    u.name, u.surname 
FROM users u
INNER JOIN user_project up ON u.user_id = up.user_id
WHERE up.project_id = @parameter

更新：假设您的Role表再次通过链接表Users_role连接到Users，那么您也需要使用此命令来选择角色名称：

SELECT 
    u.name, u.surname,
    RoleName = r.Name
FROM users u
INNER JOIN user_project up ON u.user_id = up.user_id
INNER JOIN user_roles ur ON u.user_id = ur.user_id
INNER JOIN role r ON ur.role_id = r.role_id
WHERE up.project_id = @parameter

Answer 2

SELECT users.name, users.surname
FROM users u
INNER JOIN user_project up
ON u.user_id = up.user_id
WHERE up.project_id = @parameter

Answer 3

SELECT 
       users.name, users.surname 
FROM   users 
       inner join user_project 
         on users.user_id = user_project.user_id
WHERE  user_project.project_id = @parameter