MySQL 使用搜索条件进行分组

Question

假设我有两个表，如下

files
------------
|file_id|name|
|1      |a   |
|2      |b   |
|3      |c   |


files_tags_associations
------------
|associationId|file_id|tag_id|
|1            |1      |3     |
|2            |2      |3     |
|3            |2      |4     |
|4            |3      |3     |
|5            |3      |4     |
|6            |3      |5     |
|7            |3      |6     |

问题是，如何获取标记为 3 AND 5 的文件名？在这种情况下c

SELECT f.name from files f LEFT JOIN files_tag_associations fta ON fta.file_id = f.fileId

...然后我就迷路了

Answer 1

SELECT f.name
FROM files as f
INNER JOIN (SELECT file_id
            FROM files_tags_associations
            WHERE tag_id IN (3,5)
            GROUP BY file_id
            HAVING COUNT(*) = 2) as ft
    ON f.file_id = ft.file_id