Mysql Where Column A = X and Column B = Y and or Column B = Z

Question

I'm trying to get a set of values from a pivot table where column A is equal to an array of values, so for example ID 12 has attribute_value_id equal to 3 and 9. Can this be done? I've got this far...

ID | post_id | attribute_id | attribute_value_id
8      12          1             3
9      12          2            13
10     13          1             3
11     13          2             9
12     16          1             3
13     16          2             9
88     11          1             1
89     11          2             8
90     11          3            18
91     11          4            22

The query...

select * 
from `searching_for_posts` 
where (
    select count(*) 
    from `attributes` 
    inner join `searching_for_attributes`
    on `attributes`.`id` = `searching_for_attributes`.`attribute_id` 
    where `searching_for_attributes`.`searching_for_post_id` = `searching_for_posts`.`id` 
    and (`attribute_value_id` = 3 and `attribute_value_id` = 9)
) >= 1

If I use the and then I get no values. If I use the or then I get 3 values but it should return 2. I have limited SQL experience.

Answer 1

You can do this using group by and having. Your logic is hard to follow, but it is something like this:

select post_id
from table t
where attribute_value_id in (3, 9)
group by post_id
having count(distinct attribute_id) = 2;

I would think you would want to check on attribute_id as well, but that doesn't seem to be part of the question.

EDIT:

If these are stored in another table:

select a.post_id
from attributes a join
     searching_for_attributes sfa
     on a.attribute_id = sfa.attribute_id and
        a.attribute_value_id = sfa.attribute_value_id
group by a.post_id
having count(*) = (select count(*) from searching_for_attributes);