MySQL - custom, additional columns based on foreign keys with other table

Question

I have the following database schema:

Table: products

| id | name   | content |
|----|--------|---------|
| 1  | Pen    | ...     |
| 2  | Pencil | ...     |
| 3  | Rubber | ...     |
| 4  | Ruler  | ...     |

Table: feature_types

| id | name     |
|----|----------|
| 1  | Color    |
| 2  | Material |
| 3  | ...      |
| 4  | ...      |

Table: features

| id | product_id | feature_type_id | value     |
|----|------------|-----------------|-----------|
| 1  | 1          | 1               | Red       |
| 2  | 1          | 2               | Aluminum  |
| 3  | 2          | 1               | Green     |
| 4  | 2          | 2               | Wood      |
| 5  | 3          | 1               | White     |
| 6  | 4          | 2               | Plastic   |

My question is how can I do something like this:

SELECT *, ... FROM products ...

With result:

| id | name   | content | feature_type_1 | feature_type_2 |
|----|--------|---------|----------------|----------------|
| 1  | Pen    | ...     | Red            | Aluminum       |
| 2  | Pencil | ...     | Green          | Wood           |
| 3  | Rubber | ...     | White          | NULL           |
| 4  | Ruler  | ...     | NULL           | Plastic        |

So as you see, in results we have all columns from products table and additional columns for specified feature_types. Column names correspond to their identifiers, according to the pattern: "feature_type_{ID}".

I know feature_types IDs so it is not necessary to add all possible columns feature_types. I need only 2 additional columns with ID 1 and 2.

You can't put Aluminium in and expect to get Aluminum out – Strawberry Aug 23 '19 at 09:57 — Strawberry, Aug 23 '19 at 09:57
@Strawberry good point, corrected! – ZaquPL Aug 23 '19 at 20:18 — ZaquPL, Aug 23 '19 at 20:18

ScaisEdge · Answer 1 · 2019-08-23T11:11:28.017

1

a semplified way is based on building a string using group_concat

 select  p.id, p.name, p.content , group_concat( concat(t.name,':',f.value )) all_features  
 from products p
 inner join  features f on f.product_id = p.id
 inner join  feature_types t on t.id = f.feature_type_id
 group by p.id

edited Aug 23 '19 at 11:11

answered Aug 23 '19 at 10:01

ScaisEdge

131,976
10
91
107

score 1 · Answer 2 · answered Aug 23 '19 at 10:06

Here's one solution to the part of the problem with which you are struggling...

SELECT product_id
     , MAX(CASE WHEN feature_type_id = 1 THEN value END) feature_type_1
     , MAX(CASE WHEN feature_type_id = 2 THEN value END) feature_type_2 
  FROM features 
 GROUP 
    BY product_id;
+------------+----------------+----------------+
| product_id | feature_type_1 | feature_type_2 |
+------------+----------------+----------------+
|          1 | Red            | Aluminium      |
|          2 | Green          | Wood           |
|          3 | White          | NULL           |
|          4 | Plastic        | NULL           |
+------------+----------------+----------------+
4 rows in set (0.03 sec)

or...

SELECT f1.product_id
     , f1.value feature_type_1
     , f2.value feature_type_2 
  FROM features f1 
  LEFT 
  JOIN features f2 
    ON f2.product_id = f1.product_id 
   AND f2.feature_type_id = 2 
 WHERE f1.feature_type_id = 1;

score 1 · Accepted Answer · answered Aug 23 '19 at 10:22

If you are only interested in the features: Color and Material, join the tables and group by product:

select
  p.id, p.name, p.content,
  max(case when t.name = 'Color' then f.value end) Color,
  max(case when t.name = 'Material' then f.value end) Material
from products p 
left join features f on f.product_id = p.id
left join feature_types t 
on t.id = f.feature_type_id and t.name in ('Color', 'Material')
group by p.id, p.name, p.content

I guess in your sample data you did a mistake by setting 1 instead of 2 as feature_type_id for Plastic in the table features.
See the demo.
Results:

| id  | name   | content | Color | Material  |
| --- | ------ | ------- | ----- | --------- |
| 1   | Pen    | ...     | Red   | Aluminium |
| 2   | Pencil | ...     | Green | Wood      |
| 3   | Rubber | ...     | White |           |
| 4   | Ruler  | ...     |       | Plastic   |

Fine, this works great, but I have one caveat. What if I have 20 or 30 columns in products table? Creating this query is very inelegant: ... GROUP BY p.id, p.name, p.content, p.price, p.category_id, p.type, ..., ..., ..., ..., ..., p.created_at, p.updated_at, p.deleted_at — ZaquPL, Aug 23 '19 at 19:56
If you disable ONLY_FULL_GROUP_BY you can omit these columns from GROUP BY. See these: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html and https://stackoverflow.com/questions/23921117/disable-only-full-group-by. **But** this is not how I would do it. I would prefer the not so elegant way. — forpas, Aug 23 '19 at 20:04

MySQL - custom, additional columns based on foreign keys with other table

3 Answers3