SQL 查询 - 多个内连接

Question

我对 SQL 的了解非常有限，但我必须创建一个报告查询(在 EPDM* 中)并且我对 INNER JOIN 感到迷惑:)

*EPDM 是一个项目数据管理软件，它有一个报告生成器。这需要一个格式化的查询文件。

这是一个有效的查询文件内容:

@[ListAllDoc] 
§Name [List all documents] 
§Company [Econ] 
§Description [This query lists all documents] 
§Version [1.1] 
§Arguments
[
  ProjectID pProjectID [1] [Select search folder. E.g "$\Documents", or browse for folder.]
]
§Sql 
[ 
SELECT D.Filename as 'File Name', P.Path As 'Path'
FROM Projects AS P 
INNER JOIN DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID
INNER JOIN Documents AS D ON DP.DocumentID = D.DocumentID
WHERE P.ProjectID = {pProjectID}
Order By P.Path ASC, D.Filename ASC
] 

目标是具有列 的指定路径的文件列表文件名 + 路径 + 用户名 (最新版本)

数据存储在数据库中，如下所示(表和列):

文档 => 文档 ID、项目 ID、文件名、最新版本号

DocumentsInProjects => 项目 ID、路径、文档 ID

修订 => RevNr、DocumentID、UserID

用户 => 用户 ID，用户名

文档与其路径之间的连接是带有 ProjectID 列的 DocumentsInProjects 表。

文档有多个修订版，当 Documents.DocumentID = Revisions.DocumentID 和 Documents.LatestRevisionNo = Revisions.RevNr(最新修订版的用户)时，我想从修订表中获取 UserID。

样本数据:

+------------------------------------------------------+
| Documents                                            |
+------------+-----------+----------+------------------+
| DocumentID | ProjectID | Filename | LatestRevisionNo |
+------------+-----------+----------+------------------+
| 100        | 10        | Test.txt | 3                |
+------------+-----------+----------+------------------+

+------------------------------------------------+
| DocumentsInProjects                            |
+---------------------+-------------+------------+
| ProjectID           | Path        | DocumentID |
+---------------------+-------------+------------+
| 10                  | D:\TestPath | 100        |
+---------------------+-------------+------------+

+-----------------------------+
| Revisions                   |
+-------+------------+--------+
| RevNr | DocumentID | UserID |
+-------+------------+--------+
| 1     | 10         | 55     |
+-------+------------+--------+
| 2     | 10         | 46     |
+-------+------------+--------+
| 3     | 10         | 32     |
+-------+------------+--------+

+-------------------+
| Users             |
+--------+----------+
| UserID | Username |
+--------+----------+
| 55     | Peter    |
+--------+----------+
| 46     | Mike     |
+--------+----------+
| 32     | Lucy     |
+--------+----------+

使用上面的示例数据，我想得到结果:

+-----------+-------------+------+
| File Name | Path        | User |
+-----------+-------------+------+
| Test.txt  | D:\TestPath | Lucy |
+-----------+-------------+------+

这是我现在的位置，但这当然不起作用:D

{pProjectID} 是从选择中获取的变量。

SELECT D.Filename As 'File Name', P.Path As 'Path', U.Username as 'User'
FROM Projects AS P
INNER JOIN DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID
INNER JOIN Documents AS D ON DP.DocumentID = D.DocumentID
INNER JOIN Revisions AS R ON D.DocumentID = R.DocumentID AND D.LatestRevisionNo = R.RevNr
INNER JOIN Users AS U ON U.UserID = D.UserID
WHERE P.ProjectID = {pProjectID}

请帮我纠正这个:)

更新 -> 解决方案
感谢大家对我的帮助，即使我的问题没有很好地说明并且包含错​​误，缺少元素(抱歉):)
@davidc2p 在代码更正方面对我帮助最大，基于此我做了一些修改，这段代码运行良好:

SELECT D.Filename As 'File Name', P.Path As 'Path', U.Username As 'User'
FROM Projects AS P
INNER JOIN DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID
INNER JOIN Documents AS D ON DP.DocumentID = D.DocumentID
INNER JOIN Revisions AS R ON D.DocumentID = R.DocumentID AND D.LatestRevisionNo = R.RevNr
INNER JOIN Users AS U ON R.UserID = U.UserID
WHERE P.ProjectID = {pProjectID}

谢谢你们!

Answer 1

应提供有关主键或唯一键的信息。但是考虑到您发送的查询内容有一些错误:

表 Documents 上没有用户 ID。您的查询应该从 Revisions 表访问 UserID。

此外，DocumentID 不是来自 DocumentsInProjects 的字段，您应该将此表与 ProjectID 相关联，从而获取与项目关联的所有文档。

此外，路径来自 DP 而不是 P。

SELECT D.Filename As 'File Name', DP.Path As 'Path', U.Username as 'User'
FROM Projects AS P

    INNER JOIN DocumentsInProjects AS DP 
    ON P.ProjectID = DP.ProjectID

    INNER JOIN Documents AS D 
    ON DP.ProjectID= D.ProjectID

    INNER JOIN Revisions AS R 
    ON D.DocumentID = R.DocumentID 
    AND D.LatestRevisionNo = R.RevNr

    INNER JOIN Users AS U 
    ON U.UserID = R.UserID

WHERE P.ProjectID = {pProjectID}

可能还有一些其他错误。如果您还提供带有一组示例数据的临时表创建，会更容易。

同样取决于唯一性，您的结果可能会显示重复的信息。