Filter table by multiple pivot values I have the following tables:
fabrics
| id| title   | code|
|-- |---------| ----|
| 1 | Fabric 1| FBR1|
| 2 | Fabric 2| FBR2|
| 3 | Fabric 3| FBR3|
| 4 | Fabric 4| FBR4|

attributes
| id| title      | category|
|---|------------| -----   |
| 1 | Type       | printed |
| 2 | Color      | printed |
| 3 | Composition| printed |

attribute_values
| id| attribute_id| text  |
|---|-----------  | ----- |
| 1 | 1           | soft  |
| 2 | 2           | yellow|
| 3 | 2           | blue  |
| 4 | 2           | black |
| 5 | 1           | dry   |
| 6 | 2           | green |
| 7 | 3           | cotton|
| 8 | 3           | wool  |

fabric_attribute_value
| id| attribute_value_id| fabric| attribute_id|
|---|-------------------| ------| ------------|
| 1 | 6                 | 1     | 2           |
| 2 | 7                 | 1     | 3           |
| 3 | 2                 | 2     | 2           |
| 4 | 7                 | 2     | 3           |
| 5 | 6                 | 3     | 2           |
| 6 | 8                 | 3     | 3           |
| 7 | 2                 | 4     | 2           |
| 8 | 8                 | 4     | 3           |

My problem is that I want to filter for example Fabrics with Color attribute: yellow OR green AND with the Composition attribute: cotton.
I should be getting Fabric 1 and Fabric 2 as results back.
How do I accomplish this query?
When there are multiple filters for the same attribute like Color it should be an OR inside it, but if it binds with condition with another attribute like Composition it should be AND. So for example in pseudo-code:
(Yellow(Color) OR Green(Color) AND Cotton(Composition))
every further filter you want to add simply increase the number in the IN Clauses
If you have a new attrubute you have to add another AND in the WHERE clause
As i said in the comment attribute_id in attribute_values is redundant


CREATE TABLE fabric_attribute_value
    (`id` varchar(3), `attribute_value_id` varchar(19), `fabric` varchar(6), `attribute_id` varchar(12))
;
    
INSERT INTO fabric_attribute_value
    (`id`, `attribute_value_id`, `fabric`, `attribute_id`)
VALUES

    ('1', '6', '1', '2'),
    ('2', '7', '1', '3'),
    ('3', '2', '2', '2'),
    ('4', '7', '2', '3'),
    ('5', '6', '3', '2'),
    ('6', '8', '3', '3'),
    ('7', '2', '4', '2'),
    ('8', '8', '4', '3')
;




CREATE TABLE fabrics
    (`id` int, `title` varchar(8), `code` varchar(4))
;
    
INSERT INTO fabrics
    (`id`, `title`, `code`)
VALUES
    (1, 'Fabric 1', 'FBR1'),
    (2, 'Fabric 2', 'FBR2'),
    (3, 'Fabric 3', 'FBR3'),
    (4, 'Fabric 4', 'FBR4')
;




SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric` 
                 FROM fabric_attribute_value 
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7))))
  




title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2




SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric` 
                 FROM fabric_attribute_value 
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7, 8))))


title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2
Fabric 3 | FBR3
Fabric 4 | FBR4


db&lt;&gt;fiddle here