帮助查询关系表

Question

我正试图消除我的SQL技能，并需要以下查询的帮助。我当前使用的数据库是mysql。

我想检索所有同时分配了’tag2’和’tag4’的抽认卡。根据现有表的内容（如以下摘录所示），查询应返回两行：FlashCard_ID 1和2。

我将如何制定此查询？自从我不得不做这样的事情已经有一段时间了。

mysql> select * from flashcard;
+--------------+------------+----------+
| FLASHCARD_ID | QUESTION   | ANSWER   |
+--------------+------------+----------+
|            1 | Question 1 | Answer 1 |
|            2 | Question 2 | Answer 2 |
|            3 | Question 3 | Answer 3 |
+--------------+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from tag;
+--------+------+
| TAG_ID | NAME |
+--------+------+
|      1 | tag1 |
|      2 | tag2 |
|      3 | tag3 |
|      4 | tag4 |
|      5 | tag5 |
+--------+------+
5 rows in set (0.00 sec)

mysql> select * from flashcard_tags;
+--------+--------------+
| TAG_ID | FLASHCARD_ID |
+--------+--------------+
|      2 |            1 |
|      3 |            1 |
|      4 |            1 |
|      2 |            2 |
|      4 |            2 |
|      5 |            2 |
+--------+--------------+
6 rows in set (0.00 sec)

Answer 1

SELECT  f.*
FROM    (
        SELECT  flashcard_id
        FROM    tags t
        JOIN    flashcard_tags ft
        ON      ft.tag_id = t.tag_id
        WHERE   t.name IN ('tag2', 'tag4')
        GROUP BY
                flashcard_id
        HAVING  COUNT(*) = 2
        ) ft
JOIN    flashcard f
ON      f.flashcard_id = ft.flashcard_id