Need help searching a MySQL db using a SELECT DISTINCT result

Posted by user1695645 on Stack Overflow See other posts from Stack Overflow or by user1695645
Published on 2012-09-24T21:36:26Z Indexed on 2012/09/24 21:37 UTC
Read the original article Hit count: 158

Filed under:

So I am new to MySQL and am having a bit of trouble. I have one table called book_genres and another called books.

book_genres
+-------+---------+
|book_id|  genre  |
+-------+---------+
| 1     | Horror  |
| 1     | Comedy  |
| 2     | Romance |
| 2     | Comedy  |
+-------+---------+

books
+-------+---------+
|book_id|  title  |
+-------+---------+
| 1     | A Book  |
| 2     | B Book  |
| 3     | C Book  |
+-------+---------+

I am using the following command to pull all the book_ids that have 3 selected genres:

SELECT DISTINCT a.book_id, b.genre AS genre1, c.genre AS genre2, d.genre AS genre3 FROM book_genres a JOIN book_genres b ON a.book_id = b.book_id AND b.genre LIKE 'Romance' JOIN book_genres c ON a.book_id = c.book_id AND c.genre LIKE 'Action' JOIN book_genres d ON a.book_id = d.book_id AND d.genre LIKE 'Comedy' GROUP BY book_id

What I want to do is now pull all of the book titles from the books table using the book_ids found in this search. I'm not sure if there is an easier way to do this or not, but this was all that I could come up with.

Thank you for anyone who can help!

© Stack Overflow or respective owner

Related posts about mysql