Select based on multiple ID's in the same column

user_5700525 0
Viewed 38

I have two SQL tables already built (not by me) :

One table that has all the ingredients with ID's assigned to them : 

IngredientID | IngredientName
1            | Milk
2            | Eggs
3            | Flower

and the second table that has finished products that have reference to ingredientID in the ProductIngredients column but no the product name and they are built like this : 

ProductID | ProductName | ProductIngredients
1         | Tomato Soup | NULL
2         | Pancakes    | ;1;2;3
3         | Omlette     | ;1;2

If they don't have any reference then its written NULL, and if they have, then they start with a ";" symbol and every ingredient is divided by a ";" symbol.

My goal is to join those two tables and make a select statement that returns me, instead of the ID's of the other column, the actual names of the used ingredients like this : 

ProductID | ProductName | ProductIngredients
1         | Tomato Soup | 
2         | Pancakes    | Milk, Eggs, Flower
3         | Omlette     | Milk, Eggs

Can anyone help out with this?

select join sql
1 Answers

You need a left join of Products to ProductIngredients and group by product: 

select p.ProductID, p.ProductName,
  group_concat(i.IngredientName order by i.IngredientID) ProductIngredients
from Products p left join Ingredients i
on concat(p.ProductIngredients, ';') like concat('%;', i.IngredientID,';%')
group by p.ProductID, p.ProductName

The function group_concat() works in MySql but you can find similar functions to other databases.
See the demo.
Results:

| ProductID | ProductName | ProductIngredients |
| --------- | ----------- | ------------------ |
| 1         | Tomato Soup |                    |
| 2         | Pancakes    | Milk,Eggs,Flower   |
| 3         | Omlette     | Milk,Eggs          |
user_104988280
Related