CODEWITHSUNDEEP
sql
norbre
norbre

Reputation: 97

SQL query - multiple inner join

I have very limited knowledge in SQL, but I have to create a report query (in EPDM*) and I'm lost with INNER JOIN :)

*EPDM is a Project Data Management software, and it has a report generator. This needs a formatted query file.

This is a working query file content:

@[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
]

The goal is a file list of a specified path with columns filename + path + username (of latest revision)

The data is stored the database as you see below (table and columns):

The connection between the documents and its path is the DocumentsInProjects table with ProjectID column.

There are multiple revisions on the documents, and I'd liket to get the UserID from the revision table when the Documents.DocumentID = Revisions.DocumentID and Documents.LatestRevisionNo = Revisions.RevNr (user of the latest revision).

Sample data:

+------------------------------------------------------+
| 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     |
+--------+----------+

With the sample data above I'd like to get the result:

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

Here is where I am now, but this doesn't work of course :D

{pProjectID} is a variable taken from selection.

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}

please help me to correct this :)

UPDATE -> SOLUTION Thank you for all of you to helped me, even is my question was not well specified and contains errors, missing elements (sorry for that) :) @davidc2p helped me the most with the code correction, based upon that I made a little modification and this code works well:

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}

Thank you all!

Upvotes: 4

Views: 148

Answers (1)

davidc2p
davidc2p

Reputation: 320

Information about primary keys or unique keys should be provided. But considering what you sent your query has some errors:

There's no userID on table Documents. Your query should access UserID from Revisions table.

Also, DocumentID is not a field from DocumentsInProjects, you should link this table with ProjectID and therefore get all documents associated with a project.

Also, path is from DP not 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}

There are probably some other mistakes. It would be easier if you provided also temporary table creation with a set of sample data.

Also depending on unicity, your result might show duplicate information.

Upvotes: 2

Related Questions