Select rows with multiple AND conditions that override eachother I have the following table schema:

book_id    |   attribute_id    |    attribute_value
____________________________________________________

1          |        35         |         Sci-Fi
2          |        35         |         Drama
3          |        35         |         Thriller
4          |        35         |         Romance
1          |        36         |         Author1
2          |        36         |         Author2
3          |        36         |         Author3
4          |        36         |         Author4
1          |        37         |         $300
2          |        37         |         $100
3          |        37         |         $600
4          |        37         |         $700



I tried to fetch results that have multiple conditions like in the query below from the table 'books'

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') AND attribute_id = 36 AND attribute_value IN ('Author2')


I know this is not working because I cannot have multiple AND conditions on the same column. I was looking for the following output of the query above

book_id  |
-------- |
   2     |


I tried using UNION and separating the query in 2 different queries:

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')
UNION
SELECT book_id FROM books WHERE attribute_id = 36 AND attribute_value IN ('Author2')


But obviously this is returning the same results as using the query below (replacing AND with OR

SELECT book_id FROM books WHERE (attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')) OR (attribute_id = 36 AND attribute_value IN ('Author2'))


I tried INNER JOINING the table on those conditions and I did not had any success. Not sure what I am missing. I am looking for a solution that would work with more attributes not only 2 (35,36). Any ideas?
Depending on what you need

SELECT book_id 
FROM books
GROUP BY book_id 
-- Either 'Drama' or 'Thriller', at least one
HAVING SUM( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )


or

SELECT book_id 
FROM books
GROUP BY book_id 
-- Both 'Drama' and 'Thriller'
HAVING SUM( attribute_id = 35 AND attribute_value = 'Drama' )
   AND SUM( attribute_id = 35 AND attribute_value = 'Thriller' )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )




Also you may add

WHERE ( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   OR ( attribute_id = 36 AND attribute_value = 'Author2' )


in both (this is not misprint!) queries. This will decrease the amount of records grouped using index if exists, but will add filesort instead. If the index not exists this conditions add is preferred.