Select multiple tables using INNER JOIN-CodePudding

I have 3 tables:

Table: Files

id	type	size	path	documentID	proofID	userID
1	pdf	100	/document/something-1.pdf	1	1	1
2	pdf	100	/document/something-1.pdf	1	2	1
3	pdf	100	/document/something-2.pdf	2	1	2
4	pdf	100	/document/something-2.pdf	2	1	1

Table : Documents

id	name
1	Document
2	Document 2

Table : Proofs

id	name	documentID
1	Something	1
2	Something	1
3	Something	2

Expected results :

document	proof	path
Document	2	/document/something-1.pdf
Document 2	1	/document/something-2.pdf

I want to select all documents uploaded by user 1 from table Files (userID: 1)
Then select these documents name using documentID from table Documents, count how many proof it has

I have been trying something like :

SELECT
Documents.name as document, 
COUNT(files.proofID) as proof, 
files.path as path,
FROM files
INNER JOIN Documents ON files.documentID = Documents.id
INNER JOIN Proof ON files.proofID = Proof.id
WHERE files.userID = 1

this query give something like

document	proof	path
Document	3	/document/something-1.pdf

CodePudding user response：

try this query

WITH files(id,type,size,path,docid,proofid,userid) AS ( VALUES
('1', 'pdf', '100', '/document/something-1.pdf', '1', '1', '1'),
('2', 'pdf', '100', '/document/something-1.pdf', '1', '2', '1'),
('3', 'pdf', '100', '/document/something-2.pdf', '2', '1', '2'),
('4', 'pdf', '100', '/document/something-2.pdf', '2', '1', '1')
), 
proofs(id,name,docid) AS (VALUES 
('1', 'Something', '1'), 
('2', 'Something', '1'), 
('3', 'Something', '2')
),
docs(id, name) AS (VALUES
('1','Document'),
('2','Document 2')
)
SELECT f.docid,d.name,count(p.docid) FROM files f INNER JOIN proofs p ON f.proofid=p.id INNER JOIN docs d ON d.id=f.docid WHERE userid='1' GROUP BY f.docid,d.name

the output of the query

docid   name    count
1   Document    2
2   Document 2  1