Thursday, 15 August 2013

MySQL select from second row where

MySQL select from second row where

I have three tables containing books and tags. A book can be tagged with
several tags.
What I want to do is to filter out books whish are tagged with both
'Comics' AND 'Romance'.
The first book is tagged only with 'Comics'.
The second book is tagged with both 'Comics' AND 'Romance', so this row
should be returned in our example.
The third book is tagged only with 'Fantasy'.
How do I construct a proper 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);

No comments:

Post a Comment