Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by Simon Morris
Simon Morris
16,250 Points

DISTINCT VALUES merging fields MYSQL

i have a few tables

dishes, recipe_dishes, recipes, recipe_ingredients, ingredients, ingredient_allergens, allergens

i am selecting all allergens that are related to a particular dish using mysql joins

DROP TABLE IF EXISTS dish_contents;

CREATE TEMPORARY TABLE IF NOT EXISTS dish_contents (dish_id MEDIUMINT, allergen VARCHAR(255), contents VARCHAR(255));

INSERT INTO dish_contents (SELECT rd.dish_id AS dish_id, a.name, ia.keywords FROM allergens a INNER JOIN ingredient_allergens ia ON ia.allergen_id = a.id INNER JOIN ingredients i ON ia.ing_id = i.id INNER JOIN recipe_ingredients ri ON i.id = ri.ing_id INNER JOIN recipes r ON ri.recipe_id = r.id INNER JOIN recipe_dishes rd ON (rd.recipe_id = r.id) JOIN dishes d ON d.id = rd.dish_id WHERE rd.dish_id = 11);

INSERT INTO dish_contents (SELECT rd.dish_id AS dish_id, a.name, ia.keywords FROM allergens a INNER JOIN ingredient_allergens ia ON ia.allergen_id = a.id INNER JOIN ingredients i ON i.id = ia.ing_id INNER JOIN recipe_dishes rd ON rd.ing_id = i.id WHERE rd.dish_id = 11);

the keywords field has a comma separated list of what the allergen comes from

Eg. Allergen = 'Gluten', keywords = 'barley, wheat, oats'

since there can be many ingredients in a dish and you can have more than one type of cereal containing gluten for example

Allergen = Keywords,

'Gluten' = 'Barley, Wheat',

'Gluten' = 'Oats, Barley'

How would i SELECT the DISTINCT allergen but Merge ALL the Keywords so it looks like this

Gluten = Barley, Wheat, Oats

1 Answer

shezazr
shezazr
8,275 Points
shezazr
shezazr
8,275 Points

first make sure that your allergens are not lumped together, same with your ingredients and that you have a connected table to connect dishes with ingredients and another one (if applicable) to allergens..

i cannot decipher your SQL statements so screenshots of your data/table would be awesome

Simon Morris
Simon Morris
16,250 Points

basically im creating a temporary table and inserting allergen information in per dish

allergen table consists of id, name, code

then there is a table that relates the allergen to the ingredient which is ingredient_allergens and that consists of ing_id, allergen_id, keywords(comma separated list)

so the temporary table consists of dish_id, allergen_name, keywords