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?
Best Answer
Depending on what you need
or
Also you may add
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.