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
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
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