CODEWITHSUNDEEP
mysqlsql
Justin
Justin

Reputation: 6251

help with query over relation table

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.

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)

Upvotes: 2

Views: 142

Answers (3)

Justin
Justin

Reputation: 6251

Here's another query that works. This one doesn't use a subquery and is what I ended up using in my Hibernate code.

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

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

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'

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425331

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

Upvotes: 2

Related Questions