Sequel Pro Combine Multiple lines where only 1 column is different but the rest are the same

Question

I have a table of meals and there are several id's associated with other tables on the main table.

I have another table where the ingredients for the meals are listed by ids so that I don't have to type the ingredients over and over. But when I run the following search, I get the meal name, listed multiple times because each ingredient is listed on it's own line. How can I get it to display the name, and then all the ingredients on the same line?

SELECT
    mealName, ingredient, quantity 
FROM 
    meals, mealTotal, ingredients 
WHERE 
        mealTotal.mealId = meals.MealId 
    and mealTotal.ingredientId =  ingredients.ingredientId  
ORDER BY 
    mealName;

currently shows:

Apple Pie    Apple    1
Apple Pie    Tabantha Wheat    1
Apple Pie    Goat Butter       1
Apple Pie    Cane Sugar    1

What I want it to show:

Apple Pie    Apple   1    Tabantha Wheat    1      Goat Butter    1    Cane Sugar    1

*Never* use commas in the `FROM` clause. *Always* use proper, explicit `JOIN` syntax. — Gordon Linoff, Jul 29 '17 at 13:04

score 0 · Answer 1 · answered Jul 29 '17 at 15:01

You can use GROUP_CONCAT to return all the ingredients and quantities as a list. So starting with the already existing query, you'd convert it to:

-- Concatenate all your (ingredient, quantity)
SELECT
    mealName, 
    GROUP_CONCAT(ingredient_and_quantity ORDER BY ingredient_and_quantity SEPARATOR '\t' ) AS ingredient_list
FROM
    (SELECT
        mealName, ingredient, quantity, 
        CONCAT(ingredient, ' ', quantity) AS ingredient_and_quantity
    FROM 
        meals
        JOIN mealTotal ON mealTotal.mealId = meals.mealId
        JOIN ingredients ON ingredients.ingredientId = mealTotal.ingredientId 
    ) AS q
ORDER BY 
    mealName;

This is going to give you a string with all the ingredients and corresponding quantities.

mealName  | ingredient_list                                                         
:-------- | :-----------------------------------------------------------------------
Apple Pie | Apple 1        Cane Sugar 1        Goat Butter 1        Tabantha Wheat 1

Note that I have changed your implicit JOINs to explicit ones. (Implicit JOINs are more difficult to read and error-prone, it's easy you miss one link-condition on the WHERE and you get a carteasian product you weren't expecting).

You can check all this at dbfiddle here

If you want to have the list of ingredients as different columns (ingredient_1, quantity_1, ingredient_2, quantity_2, ...), then you can start from a variation of this query, and decompose the grouped data using SUBSTRING_INDEX(str,delim,count):

-- Pivot 
SELECT
    mealName, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  1), ',', -1) AS ingredient_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  2), ',', -1) AS ingredient_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  3), ',', -1) AS ingredient_3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  4), ',', -1) AS ingredient_4,

    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  1), ',', -1) AS quantity_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  2), ',', -1) AS quantity_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  3), ',', -1) AS quantity_3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  4), ',', -1) AS quantity_4
FROM
    (SELECT
        mealName, 
        GROUP_CONCAT(ingredient ORDER BY ingredient SEPARATOR ',') AS ingredients,
        GROUP_CONCAT(quantity ORDER BY ingredient SEPARATOR ',') AS quantities
    FROM
        (SELECT
            mealName, ingredient, quantity
        FROM 
            meals
            JOIN mealTotal ON mealTotal.mealId = meals.mealId
            JOIN ingredients ON ingredients.ingredientId = mealTotal.ingredientId 
        ORDER BY
            mealName, ingredient
        ) AS q
    ) AS q2
ORDER BY 
    mealName;

That would give you something like:

mealName  | ingredient_1 | ingredient_2 | ingredient_3 | ingredient_4   | quantity_1 | quantity_2 | quantity_3 | quantity_4
:-------- | :----------- | :----------- | :----------- | :------------- | :--------- | :--------- | :--------- | :---------
Apple Pie | Apple        | Cane Sugar   | Goat Butter  | Tabantha Wheat | 1.01       | 1.04       | 1.03       | 1.02

You can check this at dbfiddle here

Note that this doesn't scale (if you need 10 columns, you'll have to do it by hand), and that some values might get repeated, because of the way STRING_INDEX works. I wouldn't recommend this approach.

Ultimately what I plan on doing is using the query to pull the information into a program. I'm new to coding and such, and just working in a console window of C# for now. So I'm looking to take the into and put the ingredient and the number into a dictionary? for that particular meal. Then When i want, I will be able to display the ingredient and quantity for that ingredient back to the user. Hope that helps a bit with understanding better how to query it from the database. — Lauren Townsend, Jul 29 '17 at 20:34
If you wanted to retrieve values and put them into a dictionary using C#, *you would have got the proper response if you have just asked that*. Actually, you were already doing the right thing, you just need to loop through your recordset, and add items to your dictionary. — joanolo, Jul 29 '17 at 20:38

Sequel Pro Combine Multiple lines where only 1 column is different but the rest are the same

1 Answers1