How to organize a complex query in SQL?

Question

SQL

Got out of strength. How to implement the following:
there is a table "product_attribute" with the following structure (with a mini example):
product_id attribute_id text
105 5 100
105 8 55
105 9 92
106 5 100
106 8 95
106 9 93
107 5 100
107 8 55
107 9 80
and a table with categories "product_category"
product_id category_id
105 8
106 8
107 8
We need to get a filter where the user specifies a category and several attributes (for example: category - 8, selected attributes attribute_id 5 with text 100 and attribute_id 8 with text 55. we need to select the remaining attribute options in this case should return attribute_id=9, text =92 and attribute_id=9, text=80
I'll explain further: this is a tire filter on a site where attributes such as radius, width, height, seasonality are used.The filter works ajax.Each time one of the selectors is selected, it updates the unselected ones by loading only allowable values
​​what are the thoughts?

Answer 1

Something like this:

SELECT DISTINCT `a`.`attribute_id`, `a`.`text`
  FROM (
    SELECT DISTINCT `p`.`id`
      FROM `product` AS `p`
      JOIN `product_category` AS `c` ON `c`.`product_id` = `p`.`id` 
        AND `c`.`category_id` = :categoryId
      JOIN `product_attribute` AS `a1` ON `a1`.`product_id` = `p`.`id`
        AND `a1`.`attribute_id` = :attribute1 AND `a1`.`text` = :attrValue1
      JOIN `product_attribute` AS `a2` ON `a2`.`product_id` = `p`.`id`
        AND `a2`.`attribute_id` = :attribute2 AND `a2`.`text` = :attrValue2
      ...
      JOIN `product_attribute` AS `aN` ON `aN`.`product_id` = `p`.`id`
        AND `aN`.`attribute_id` = :attributeN AND `aN`.`text` = :attrValueN
  ) AS `pr`
  JOIN `product_attribute` AS `a` ON `a`.`product_id` = `p`.`id`
  WHERE `a`.`attribute_id` NOT IN (:attribute1, :attribute2,... attributeN)
  ORDER BY `a`.`attribute_id, `a`.`text`

Answer 2

select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id  not in (
  select pa2.product_id from product_attribute pa2
     where (pa2.attribute_id=9 and pa2.text=92) or (pa2.attribute_id=8 and pa2.text=55)
) and
not((pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and  pa.text=55))

Expressions by type (pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and pa.text=55) must be generated dynamically or
Create a third table filtr_sess (sid, attribute_id, text) where to throw the current one before calling session + selected attributes. Then the call can be made only by Sid and Category_Id.
Something like this (I am writing without access to the DBMS from someone else's PC, please do not blame the syntax):

select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb, filtr_sess fs
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id  not in (
select pa2.product_id from product_attribute pa2, filtr_sess s
     where (pa2.attribute_id=s.id and pa2.text=s.text and s.sid=X)
) and fs.sid=X and pa.attribute_id <> fs.id and pa.text <> fs.text