CODEWITHSUNDEEP
mysqlsql
merlin
merlin

Reputation: 2917

How to retrieve values from normalized table in one line per dataset?

I have stored data into several MySQL 5.x tables in order to normalize, now I am struggling on how to retrieve this data in one line per dataset.

E.g.

Table 1: articles, holding also 2 values in this example per article

article_id | make | model
1            Audi   A3

Table 2: article_attributes, where one article can have several attributes

article_id | attr_id
1            1
1            2
2            1

Table 3: article_attribute_names

attr_id | name
1         Turbo
2         Airbag

Now I want to retrieve it, with one line per dataset

e.g.

SELECT a.*, attr_n.name AS function
FROM `articles` a
LEFT JOIN article_attributes AS attr ON a.article_id = attr.article_id
LEFT JOIN article_attribute_names AS attr_n ON attr_n.attr_id = attr.attr_id
-- group by attr.article_id

This will gives me:

article_id | Make | Model  | function
1            Audi   A3       Turbo
1            Audi   A3       Airbag

But I am looking for something like this:

article_id | Make | Model  | function1 | function2
1            Audi   A3       Turbo       Airbag

Is this even possible, and if yes, how?

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

The simplest method is to put the values into a delimited field using group_concat():

SELECT a.*, GROUP_CONCAT(an.name) AS functions
FROM articles a LEFT JOIN
     article_attributes aa
     ON a.article_id = aa.article_id LEFT JOIN
     article_attribute_names aan
     ON aan.attr_id = aa.attr_id
GROUP BY a.article_id;

Aggregating by article_id is okay, assuming that the id is unique (or equivalently declared as a primary key).

If you actually want the results in separate columns, that is more challenging. If you know there are at most two (as in your example), just use aggregation:

SELECT a.*, MIN(an.name) AS function1,
       (CASE WHEN MIN(an.name) <> MAX(an.name)
             THEN MAX(an.name)
        END) as function2
FROM articles a LEFT JOIN
     article_attributes aa
     ON a.article_id = aa.article_id LEFT JOIN
     article_attribute_names aan
     ON aan.attr_id = aa.attr_id
GROUP BY a.article_id;

Upvotes: 2

Related Questions