I am trying to retrieve datasets from a normalised MySQL 5.7 table shema where I am struggling to get the values.
There are 4 tables: https://www.db-fiddle.com/f/q2PJZVegdWXnpkotN2utu2/0
Table1: articles
article_id | title
1 First Car
2 Second Car
Table2: articles_attr
article_id | attr_id
1 1
1 2
1 3
1 5
2 3
2 4
Table3: attr_groups
attr_id | attr_group_id | attribute
1 1 red
2 2 diesel
3 3 automatic
4 3 airbag
5 3 radio
Table4: attr_groups_names
attr_group_id | name
1 color
2 engine
3 features
Now I would like to retrieve all datasets (car1, car2, ..) with all attributes where the ones with multiple attributes per group get agregated.
e.g.
article_id | title | color | engine | features
1 Car 1 red diesel automatic,radio
2 ...
The amount of groups is huge (20+), so I would like to avoid to many joins.
My best shot:
SELECT
a.article_id,
a.title,
GROUP_CONCAT(CASE attr.attr_group_id WHEN 26 THEN cat.attr_de END) AS functions,
GROUP_CONCAT(CASE attr.attr_group_id WHEN 27 THEN cat.attr_de END) AS miscellaneous
FROM articles_attr AS attr
INNER JOIN articles a ON a.article_id = attr.article_id
INNER JOIN articles_attr AS cat ON cat.attr_id = attr.attr_id
GROUP BY a.article_id
LIMIT 3
How can this be done?