帮助查询关系表 [英] help with query over relation table

问题描述

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

I'm trying to knock the rust off my SQL skills and need some help with the following query. The database i'm currently using is mysql.

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

I want to retrieve all the FlashCards that are assigned BOTH 'tag2' and 'tag4'. Based on the contents of the existing table (as seen in the excerpt below) the query should return two rows: FlashCard_ID 1 and 2.

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

How would I formulate this query? It's been a while since I've had to do something like this.

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)

推荐答案

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

这篇关于帮助查询关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！