MySQL查询需求：获取指定用户参与的项目信息

用PHP开发小型项目管理CMS，需编写MySQL查询语句，从projects、projectInfo、projectUsers、accounts四张表中，查询指定用户参与的所有项目（参与关系存于projectUsers表），最终PHP脚本会通过$_SESSION替换用户ID，让用户查看自己被分配的项目。

已有的全项目查询语句（能获取每个项目的最新名称、描述等信息）：

SELECT p.projectId, p.projectInfoId, p.userId, a.creatorId, p.updatedTimestamp, p.projectName, a.timestamp, a.projectType, accounts.firstname, accounts.lastname 
FROM projects AS a 
JOIN projectInfo AS p ON p.projectInfoId = ( SELECT pi.projectInfoId FROM projectInfo AS pi WHERE pi.projectId = a.id ORDER BY pi.updatedTimestamp DESC LIMIT 1 ) 
INNER JOIN accounts ON a.creatorId=accounts.id 
WHERE a.deleted = 0

此前尝试添加INNER JOIN projectUsers ON 1=projectUsers.userId无结果，子查询、IF EXISTS等方法也未成功。

核心思路 #

通过INNER JOIN关联projectUsers表，关联条件为projects.id = projectUsers.projectId，同时在WHERE子句中指定用户ID，并用DISTINCT避免同一项目因多个参与用户导致的重复结果。

最终查询语句 #

SELECT DISTINCT p.projectId, p.projectInfoId, p.userId, a.creatorId, p.updatedTimestamp, p.projectName, a.timestamp, a.projectType, accounts.firstname, accounts.lastname 
FROM projects AS a 
JOIN projectInfo AS p ON p.projectInfoId = ( SELECT pi.projectInfoId FROM projectInfo AS pi WHERE pi.projectId = a.id ORDER BY pi.updatedTimestamp DESC LIMIT 1 ) 
INNER JOIN accounts ON a.creatorId=accounts.id 
INNER JOIN projectUsers ON a.id = projectUsers.projectId -- 关联项目与参与用户
WHERE a.deleted = 0 
AND projectUsers.userId = ? -- 这里替换为目标用户ID

PHP中安全替换用户ID（使用预处理语句） #

// 假设已通过SESSION获取用户ID
$userId = $_SESSION['user_id'];

// 预处理语句防止SQL注入
$stmt = $pdo->prepare("
    SELECT DISTINCT p.projectId, p.projectInfoId, p.userId, a.creatorId, p.updatedTimestamp, p.projectName, a.timestamp, a.projectType, accounts.firstname, accounts.lastname 
    FROM projects AS a 
    JOIN projectInfo AS p ON p.projectInfoId = ( SELECT pi.projectInfoId FROM projectInfo AS pi WHERE pi.projectId = a.id ORDER BY pi.updatedTimestamp DESC LIMIT 1 ) 
    INNER JOIN accounts ON a.creatorId=accounts.id 
    INNER JOIN projectUsers ON a.id = projectUsers.projectId 
    WHERE a.deleted = 0 
    AND projectUsers.userId = ?
");
$stmt->execute([$userId]);
$userProjects = $stmt->fetchAll(PDO::FETCH_ASSOC);

以用户ID=35为例，查询结果会仅返回该用户参与的项目155的最新信息：

原错误原因说明 #

之前使用INNER JOIN projectUsers ON 1=projectUsers.userId的条件完全错误，该条件要求projectUsers.userId的值等于1，而非关联项目ID，导致无法匹配正确的项目关联关系，因此无结果返回。

内容的提问来源于stack exchange，提问作者RyanR