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: 160
mysql
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