帮助查询关系表

Question

我试图敲掉我的SQL技能的锈迹，并需要一些帮助以下查询。我目前使用的数据库是mysql。

我想要检索所有分配了'tag2'和'tag4'的FlashCards。根据现有表格的内容（如以下摘录所示），查询应返回两行：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 

Answer 2

SELECT f.* 
FROM flashcard f 
    INNER JOIN flashcard_tags ft1 ON f.FLASHCARD_ID = ft1.FLASHCARD_ID 
    INNER JOIN tag t1 ON ft1.TAG_ID = t1.TAG_ID AND t1.NAME = 'tag2' 
    INNER JOIN flashcard_tags ft2 ON f.FLASHCARD_ID = ft2.FLASHCARD_ID 
    INNER JOIN tag t2 ON ft2.TAG_ID = t2.TAG_ID AND t2.NAME = 'tag4' 

Answer 3

这里的另一个查询工作。这个不使用子查询，而是我最终在我的Hibernate代码中使用的。

select fc.FLASHCARD_ID, 
     fc.QUESTION, 
     fc.ANSWER 
from FLASHCARD fc 
      inner join FLASHCARD_TAGS fc_t 
       on fc.FLASHCARD_ID=fc_t.FLASHCARD_ID 
      inner join TAG t 
       on fc_t.TAG_ID=t.TAG_ID 
where t.Name in ('tag2', 'tag4') 
group by fc.FLASHCARD_ID 
having count(t.TAG_ID)=2