I have two tables
我有两张桌子
documents: id, folder_id
folders: id, title
I want to retrieve a list of folders and count of how many files relate to that folder (documents.folder_id = folders.id)
我想检索文件夹列表以及与该文件夹相关的文件数量（documents.folder_id = folders.id）
I tried this
我试过这个
select
f.title, count(d.id) as file_count
from
folders f
left join
documents d on f.id = d.folder_id
But that only returns one row, with the total number of rows in the documents table
但是只返回一行，其中包含文档表中的总行数
2 个解决方案
#1
2
You're missing a GROUP BY clause to return a row per f.title.
您缺少GROUP BY子句以返回每个f.title的行。
select
f.title, count(d.id) as file_count
from
folders f
left join
documents d on f.id = d.folder_id
GROUP BY f.title
Note that the above will omit empty folders, I believe. If you want a zero count for the empty folders, use COUNT(d.*) instead:
请注意，以上将省略空文件夹，我相信。如果要为空文件夹计算零，请使用COUNT（d。*）代替：
select
f.title, count(d.*) as file_count
from
folders f
left join
documents d on f.id = d.folder_id
GROUP BY f.title
#2
1
SELECT F.id, F.title, COUNT(D.*)
FROM folders AS F
LEFT OUTER JOIN documents AS D ON D.folder_id = F.id
GROUP BY F.id;
#1
2
You're missing a GROUP BY clause to return a row per f.title.
您缺少GROUP BY子句以返回每个f.title的行。
select
f.title, count(d.id) as file_count
from
folders f
left join
documents d on f.id = d.folder_id
GROUP BY f.title
Note that the above will omit empty folders, I believe. If you want a zero count for the empty folders, use COUNT(d.*) instead:
请注意，以上将省略空文件夹，我相信。如果要为空文件夹计算零，请使用COUNT（d。*）代替：
select
f.title, count(d.*) as file_count
from
folders f
left join
documents d on f.id = d.folder_id
GROUP BY f.title
#2
1
SELECT F.id, F.title, COUNT(D.*)
FROM folders AS F
LEFT OUTER JOIN documents AS D ON D.folder_id = F.id
GROUP BY F.id;