MySQL – Filter Table by Multiple Pivot Values

MySQLpivot

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))

Best Answer

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<>fiddle here

Related Solutions

SQL Server – Creating Multiple Table Pivot

I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is:

Creating tables with data

CREATE TABLE #sales (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #sales VALUES (1,2012,9,5,3,4);
INSERT INTO #sales VALUES (2,2013,7,2,6,3);
INSERT INTO #sales VALUES (3,2014,6,5,6,1);
GO
CREATE TABLE #quantity (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #quantity VALUES (1,2012,14,54,13,49);
INSERT INTO #quantity VALUES (2,2013,8,12,17,31);
INSERT INTO #quantity VALUES (3,2014,16,25,25,3);
GO
CREATE TABLE #orders (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #orders VALUES (1,2012,2,5,3,9);
INSERT INTO #orders VALUES (2,2013,1,2,7,1);
INSERT INTO #orders VALUES (3,2014,1,5,5,2);
GO

And the query:

SELECT PK, CAST(Year AS CHAR(4)) + '_' + Year_Week, Sales, Quantity, Orders
FROM
    (SELECT s.PK, s.Year
        , s.Wk1, s.Wk2, s.Wk3, s.Wk4
        , q.Wk1 AS qWk1, q.Wk2 AS qWk2, q.Wk3 AS qWk3, q.Wk4 AS qWk4
        , o.Wk1 AS oWk1, o.Wk2 AS oWk2, o.Wk3 AS oWk3, o.Wk4 AS oWk4
    FROM #sales s
    INNER JOIN #quantity q ON s.PK = q.PK AND s.YEAR = q.YEAR
    INNER JOIN #orders o ON s.PK = o.PK AND s.YEAR = o.YEAR) p
UNPIVOT
    (Sales FOR Year_Week IN
        (Wk1, Wk2, Wk3, Wk4)
    )AS unpvt_s
UNPIVOT
    (Quantity FOR Year_Week_q IN
        (qWk1, qWk2, qWk3, qWk4)
    )AS unpvt_q
UNPIVOT
    (Orders FOR Year_Week_o IN
        (oWk1, oWk2, oWk3, oWk4)
    )AS unpvt_o
WHERE 'q'+Year_Week = Year_Week_q
    AND 'o'+Year_Week = Year_Week_o;
GO

MySQL – Pivot Table with Dynamic Rows

You could do something along the following lines:

select 
    s1.grade, 
    s1.firstname as s1_fname, 
    s2.firstname as s2_fname, 
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade 
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade 
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade 
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.

Best Answer

Related Solutions

SQL Server – Creating Multiple Table Pivot

MySQL – Pivot Table with Dynamic Rows

Related Question