CODEWITHSUNDEEP
mysqlsqlrow
Tomasz Szymanek
Tomasz Szymanek

Reputation: 499

How do i include multiple row reference in one row?

I have table pizza. It includes fields like cost, id, name (of pizza), and ingredients. I also have table ingredients, with name of ingredient, and id. How to i put in table pizza ingedients for example 1,2,4,15, and be able to get that ingredients name from table ingredients? 

Table Pizza:
id   name          cost    ingredients
1    Vegie Pizza   12,59   1,2

Table Ingredients
id   name
1    cheese
2    broccoli
3    pepperoni

I would like to get for egzample name and ingredients:

Vegie Piza - cheese, broccoli - 12,59

Or be able to order Vegie Pizza with pepperoni.

How do i connect this two tables in a way I described?

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You should have a table PizzaIngredients, with one row per pizza and per ingredient in the table. Someones, one doesn't have control over the data structure being used. If so, there is a solution in MySQL:

select p.name, p.cost, group_concat(i.name)
from pizza p join
     ingredients i
     on find_in_set(i.id, p.ingredients) > 0
group by p.name, p.cost;

However, a junction/association table is a much better way to store such data in a relational database.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

First of all donot store relations as comma separated values instead use junction table to relate 2 entities for this see Database Normalization

For your current solution you need to use find_in_set in join condition

select p.id,group_concat(i.name) ingredients,p.cost
from 
Pizza p
join Ingredients i on(find_in_set(i.id,p.ingredients) > 0)
group by p.id

Fiddle Demo

Upvotes: 0

Related Questions