选择所有关联的数据并组合

问题描述

我有一个表 documents 和表 labels.我有第三张表用于多对多关联，称为 doc_labels.

I've a table documents and table labels. I've a third table for the many-to-many association called doc_labels.

我在构建 SQL 语句以选择与 label1 和 label2

I've problems to build my SQL statement to select all documents that are accociated with label1 and label2

我尝试了以下方法:

SELECT * FROM documents d RIGHT JOIN doc_labels dl
ON(d.id = dl.doc_id)
WHERE dl.label_id = 1 AND dl.label_id = 2

此语句将不返回任何内容.

This statement will return nothing.

SELECT * FROM documents d RIGHT JOIN doc_labels dl
ON(d.id = dl.doc_id)
WHERE dl.label_id = 1 OR dl.label_id = 2

此语句将返回带有 label1 OR label2

This statement will return alle documents with the label1 OR label2

如何选择带有 label1 和 label2 的所有文档.

How can I select all documents with label1 AND label2.

感谢您的帮助.

推荐答案

你可以这样做

SELECT * FROM documents d
RIGHT JOIN doc_labels dl
ON(d.id = dl.doc_id)
WHERE dl.label_id IN(1,2)
GROUP BY d.id
HAVING COUNT(DISTINCT dl.label_id) >= 2 /*this will give you the documents that must have lable 1,2 and can have more lables*/

或者如果您需要只有标签 1 和 2 的文档，则更改

Or if you need the documents with only label 1 and 2 then change

HAVING COUNT(DISTINCT dl.label_id) = 2

这篇关于选择所有关联的数据并组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！