MySQL selects from the second row where

Question

I have three tables containing books and tags. You can tag multiple tags in a book.

What I want to do is filter out books in which both Comics and Romance are labeled.

• The first book is marked only by "Comics."
• The second book is marked as "Comics" AND "Romance", so this line should be returned in our example.
• The third book is marked only by Fantasy.

How to create the right query to find the second book?

.. select book_id from books b, tags t, tags_to_books tb where ((FIND TAGS_ID '7' AND '8')) ..

Table design:

DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`book_id`) ); INSERT INTO `books` (`book_id`) VALUES (1), (2), (3); DROP TABLE IF EXISTS `tags`; CREATE TABLE `tags` ( `tag_id` int(11) NOT NULL AUTO_INCREMENT, `tag_name` varchar(11) NOT NULL DEFAULT '', PRIMARY KEY (`tag_id`) ); INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES (7,'Comics'), (8,'Romance'), (9,'Fantasy'); DROP TABLE IF EXISTS `tags_to_books`; CREATE TABLE `tags_to_books` ( `book_id` int(11) NOT NULL, `tag_id` int(11) NOT NULL ); INSERT INTO `tags_to_books` (`book_id`, `tag_id`) VALUES (1,7), (2,7), (2,8), (3,9); 

Answer 1

You can use something like this, which actually matches the intersection operator in other SQL languages:

 select comics.book_id from ( select b.book_id from books b join tags_to_books tb on tb.book_id = b.book_id join tags t on tb.tag_id = t.tag_id where t.tag_id = 7) as comics join ( select b.book_id from books b join tags_to_books tb on tb.book_id = b.book_id join tags t on tb.tag_id = t.tag_id where t.tag_id = 8) as romance on comics.book_id = romance.book_id 

This may be a little optimized, but the idea is to get all the books that are in Fantasy, and then all the books that are in Romance, and then return the books that are in both. If you needed to create a query where you need more criteria, you simply copy the JOIN clause and update the tag identifier.

SQL Script Link

Answer 2

If I understand you correctly, the same book may appear several times in the tag table. Right?

Have you tried something like

 SELECT book_id FROM books WHERE book_id NOT IN (SELECT comics.book_id FROM tags_to_books as comics JOIN tags_to_books as romance ON comics.book_id = romance.book_id WHERE comics.tag_id = 7 AND romance.tag_id = 8) 

?

Answer 3

It is hard to read, but:

 SELECT B.book_id FROM Books B INNER JOIN tags_to_books ttb ON ttb.book_ID = b.book_ID JOIN tags t ON t.tag_ID = ttb.Tag_ID WHERE t.tag_ID IN(7) AND t.tag_ID IN(8) 

I did not compile this, but should work

OR as described by Joe, you can do this with tag_name

 SELECT B.book_id FROM Books B INNER JOIN tags_to_books ttb ON ttb.book_ID = b.book_ID JOIN tags t ON t.tag_ID = ttb.Tag_ID WHERE t.tag_name = 'Comics' AND t.tag_name = 'Romance' 

Answer 4

If you only need book_id (and not, say, the name of the book or the text of the tag, this works (but requires changing the magic number described below if you have a different number of tags in your request):

 SELECT book_id FROM tags_to_books WHERE tag_id IN (7, 8) GROUP BY book_id HAVING COUNT(DISTINCT book_id, tag_id) = 2; 

This 2 is the number of tags you need to change if there were more or less tags in your request.

Answer 5

Here is sql: http://sqlfiddle.com/#!2/fc556/6 Hope this helps