SQL join mapping table and get back columns not rows

Question

since 2 days I'm trying to find a solution...

I have two tables:

-- components -- colums: id | name | description

-- components_ingredients -- colums: component_id | ingredient_id

=> one component can have multiple ingredients

so when I join the tables with my statement:

SELECT * FROM components c INNER JOIN components_ingredients ci ON c.id = ci.component_id

I get back one row for every ingredient in table ci. But I want to get back only one row with the matched ingredients as additional columns like:

Is this possible and when how??

Thanks

What you are looking for is PIVOT. There are multiple answers to similar questions. Here is one: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Kevin, Apr 02 '17 at 14:56

score 0 · Answer 1 · answered Apr 02 '17 at 14:55

You can try using MySQL's GROUP_CONCAT() function to create a CSV list of the ingredients for each given component.

SELECT c.id, c.name, c.description, ci.ingredients
FROM components c
INNER JOIN
(
    SELECT component_id, GROUP_CONCAT(ingredient_id) AS ingredients
    FROM components_ingredients
    GROUP BY component_id
) ci
    ON c.id = ci.component_id

Note that as @Gordon pointed out, you might be able to do without the subquery I used, but in general you might need it. The reason Gordon's query works, even according to the ANSI standard, is a given id in the components table should uniquely determine the name and description. Hence, it is OK to include those columns while using GROUP BY, because there is no ambiguity involved.

score 0 · Answer 2 · answered Apr 02 '17 at 14:55

It is hard to put the ingredients in separate columns, because you don't now how many there are.

Much easier is to concatenate them together into a string in one column:

SELECT c.*, GROUP_CONCAT(ci.component_id) as component_ids
FROM components c INNER JOIN
     components_ingredients ci
     ON c.id = ci.component_id
GROUP BY c.id;

Note: It is generally bad practice to include columns in the SELECT that are not in the GROUP BY. However, it is okay in this case, because components.id uniquely identifies each row. This functionality is even specified as okay in the ANSI standard -- although few databases actually implement it.

SQL join mapping table and get back columns not rows

2 Answers2