There is a table of products and attributes 1 to n.

p_id desc 1 tovar1 2 tovar2 p_id attr_id val 1 23 "hello" 1 34 "FDFD"

Need to filter by multiple attributes, is it ok to write multiple joins, or as best as possible?

 select t1.* from tovar t1 inner join attr t2 on (t2.p_id=t1.p_id) inner join attr t3 on (t3.p_id=t1.p_id) where (t2.attr_id=23 and t2.val="hello") and (t3.attr_id="34" and t3.val="FDFD")

    3 answers 3

    Does this fit your intentions?

     select t1.p_id, `desc` from tovar t1 inner join attr t2 on (t2.p_id=t1.p_id) where (t2.attr_id=23 and t2.val='hello') or (t2.attr_id=34 and t2.val='FDFD') group by t1.p_id, `desc` having count(*) = 2

      Try to avoid or where possible, because there will be brakes due to the fact that indexes will not be used effectively.
      You have the correct query, but it is better to rewrite (in terms of readability and ease of modification for other conditions) in this form:

       select t1.* from tovar t1 inner join attr t2 on t2.p_id=t1.p_id and t2.attr_id=23 inner join attr t3 on t3.p_id=t1.p_id and t3.attr_id=34 where t2.val="hello" and t3.val="FDFD"

      those. the part with join'ami is fixed, and where it can change, it will be more difficult to get confused.

        I think this is not normal.

        Maybe better this way:

         select t1.* from tovar t1 inner join attr t2 on (t2.p_id=t1.p_id) where (t2.attr_id=23 and t2.val="hello") or (t2.attr_id="34" and t2.val="FDFD")
        • If I am not mistaken, now he will return to me even if there is only one attribute of them, and for me both to be taken into account. - vkovalchuk88
        • then instead of or put and , like you, only one inner join - IVsevolod
        • Then if there are two attributes, the string will not be returned. I asked the stack of overflies and asked them to say so on stackoverflow.com/questions/18565081/... select p. val = 'avalue1')> 0 and sum (a.aid = 2 and a.val = 'avalue2)> 0; - vkovalchuk88