mysql - 一对多关系查询

Question

我真的对如何创建适当的 select 语句很困惑，如果您能提供任何指导，我将不胜感激。

我创建了一个迷你文档管理系统，允许用户上传文件并按类别对这些文件进行分类。每个文件都可以选择一个或多个类别。以下是我的表格:

表:文件(pKey(主键)、file_name、file_description、file_path)

pKey  file_name  file_description        file_path
1     IT001.DOC  Network Design Document /common/it/
2     IT002.DOC  Desktop Standards       /common/it/
3     IT003.DOC  Laptop Standards        /common/it/

除了 IT 部门还有其他部门，所以路径字段也会发生变化(只是想我会把那一点数据扔进去)

表:类别(pKey(主键),category_description)

pKey  category_description
1     Central Missouri Campus
2     Eastern Missouri Campus
3     Western Missouri Campus
4     Desktops
5     Laptops
6     Networks
7     Printers

当然还有其他类别，这只是一个示例

表:category_xref(pKey(主键),fk_file_id,fk_category_id)

pKey  fk_file_id  fk_category_id
1     1           1
2     1           2
3     1           6
4     2           2
5     2           3
6     2           4
7     3           1
8     3           2
9     3           3
10    3           5

当用户搜索相关文档时，他们会看到一个带有类别复选框的表单。通过选择 Central，他们将获得所有已标记为 Central 的文件。通过选择 Desktops，他们可以获得任何已标记为 Desktops 的文档。但是，当他们选择 Central AND Desktops 时，他们会得到任何属于 Central OR Desktops 的文档。我需要弄清楚如何只获取那些既是 Central AND Desktops 又是他们选择的任何其他复选框的文档，并排除任何不包含所有选中复选框的文档。

SELECT f.pkID, f.file_name, f.file_description, f.file_path, cox.fk_category_id 
FROM files f
JOIN category_xref  cox ON cox.fk_file_id = f.pkID
WHERE cox.fk_category_id IN (59, 69)
ORDER BY f.file_name ASC, cox.fk_category_id ASC

Answer 1

最简单的方法可能是:

SELECT f.pkID, f.file_name, f.file_description, f.file_path
FROM files f
JOIN category_xref  cox ON cox.fk_file_id = f.pkID
WHERE cox.fk_category_id IN (59, 69)
GROUP BY f.pkID
HAVING count(distinct cox.fk_category_id)=2
ORDER BY f.file_name ASC