SQL doubled results in query for 2 tables connected with same id

Question

I have these two tables:

meal:

CREATE TABLE `meal` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `category` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

ingredient:

 CREATE TABLE `ingredient` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `product_name` varchar(30) NOT NULL,
  `proteins` varchar(30) NOT NULL,
  `carbos` varchar(30) NOT NULL,
  `fat` varchar(30) NOT NULL,
  `total_calories` varchar(30) NOT NULL,
  `meal_id` int(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

The meal_id field comes from the meal table.

I have some data filled already:

+----+------------+-----------+
| id | name       | category  |
+----+------------+-----------+
| 46 | ser        | sniadanie |
| 47 | pizza      | kolacja   |
| 48 | tagiatelle | obiad     |
| 49 | tagiatelle | obiad     |
| 50 | tagiatelle | obiad     |
+----+------------+-----------+


+----+--------------+----------+--------+-----+----------------+---------+
| id | product_name | proteins | carbos | fat | total_calories | meal_id |
+----+--------------+----------+--------+-----+----------------+---------+
| 61 | indyk        | 2        | 2      | 2   | 2              |      46 |
| 62 | makaron      | 2        | 2      | 2   | 2              |      46 |
| 63 | indyk        | 2        | 2      | 2   | 2              |      47 |
| 64 | makaron      | 2        | 2      | 2   | 2              |      47 |
| 65 | indyk        | 2        | 2      | 2   | 2              |      48 |
| 66 | makaron      | 2        | 2      | 2   | 2              |      48 |
| 67 | indyk        | 2        | 2      | 2   | 2              |      49 |
| 68 | makaron      | 2        | 2      | 2   | 2              |      49 |
| 69 | indyk        | 2        | 2      | 2   | 2              |      50 |
| 70 | makaron      | 2        | 2      | 2   | 2              |      50 |
+----+--------------+----------+--------+-----+----------------+---------+

My current query is:

SELECT meal.name AS 'meal_name', meal.category, ingredient.product_name,
    ingredient.proteins, ingredient.carbos, ingredient.fat, 
    ingredient.total_calories 
FROM meal 
JOIN ingredient WHERE meal.id = ingredient.meal_id;

which gives:

    +------------+-----------+--------------+----------+--------+-----+----------------+
| meal_name  | category  | product_name | proteins | carbos | fat | total_calories |
+------------+-----------+--------------+----------+--------+-----+----------------+
| ser        | sniadanie | indyk        | 2        | 2      | 2   | 2              |
| ser        | sniadanie | makaron      | 2        | 2      | 2   | 2              |
| pizza      | kolacja   | indyk        | 2        | 2      | 2   | 2              |
| pizza      | kolacja   | makaron      | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | indyk        | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | makaron      | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | indyk        | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | makaron      | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | indyk        | 2        | 2      | 2   | 2              |
| tagiatelle | obiad     | makaron      | 2        | 2      | 2   | 2              |
+------------+-----------+--------------+----------+--------+-----+----------------+

The problem is that in this case there are 2 ingredients for a meal so the meal name is printed twice. Is there any way to display meal_name and category once and related ingredients in a same row?

Thanks! Cheers!

You should use group by on meal.name, and use the function group_concat. Have a look at this other answer: https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — Alex Zen, Mar 24 '18 at 11:27

score -1 · Answer 1 · answered Mar 24 '18 at 11:40

SELECT meal.name AS 'meal_name', meal.category, 
GROUP_CONCAT(ingredient.product_name) AS product, 
GROUP_CONCAT(ingredient.proteins) AS proteins, 
GROUP_CONCAT(ingredient.carbos) AS carbos, GROUP_CONCAT(ingredient.fat) 
AS fat, GROUP_CONCAT(ingredient.total_calories) AS calories FROM meal 
JOIN ingredient WHERE meal.id = ingredient.meal_id GROUP BY 
ingredient.meal_id;

Nishant Gupta · Accepted Answer · 2018-03-24T11:54:40.017

Here is your solution for your problem:

SELECT Meal_Name,Category,Product_Name,Carbos,Fat,Total_Calories
FROM (
  SELECT meal_id,meal.name AS 'meal_name', meal.category, 
  ingredient.proteins, ingredient.carbos, ingredient.fat,
  ingredient.total_calories,
  GROUP_CONCAT(ingredient.product_name SEPARATOR ',') AS Product_name
  FROM meal 
  JOIN ingredient 
  WHERE meal.id = ingredient.meal_id 
  GROUP BY meal_id,meal_name,category,proteins,carbos,fat,total_calories
 ) AS Tbl
;

Follow the link to the demo:

http://www.sqlfiddle.com/#!9/36f55/11

SQL doubled results in query for 2 tables connected with same id

2 Answers2