Welcome to the Treehouse Community
Looking to learn something new?
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