Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools

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

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

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