Mysql Join a group_concat where reference ID is same

[dsmith01]

I have a complex query I've been wrestling with for 2 days and can'seem to get it to run. I've tried LEFT JOINs and sub-querys and GROUP_CONCATs and still can't seem to get this to work right! What am I doing doing wrong?

Lets say I have 2 tables in a recipe database:

recipe
--------------------------------------
| id | name | cat | instructions |
--------------------------------------
| 1 | Tacos | Mex. | Mix and Serve |
--------------------------------------
| 2 | Kabobs | Beef | Combine/Grill |
--------------------------------------

ingredients
------------------------------------------------
| id | recipe | qty | measurement | ingredient |
------------------------------------------------
| 1 | 1 | 1 | lb | Beef |
------------------------------------------------
| 2 | 1 | 1 | md | Onion |
------------------------------------------------
| 3 | 2 | 1 | lb | Beef |
------------------------------------------------

The goal is to result a row that looks like:

---------------------------------------------------
| id | name | cat | instructions | ingredients |
---------------------------------------------------
| 1 | Tacos | Mex. | Mix and Serve | Beef, Onion |
---------------------------------------------------

The closest I've got is:

SELECT DISTINCT recipe, (SELECT group_concat( ingredient ) 
FROM ingredients) AS ingredients 
FROM ingredients

but it seems to group_concat all results for ingredient into each resulted row. Any help is greatly appriciated! Thanks!

[smantscheff]

You forgot the GROUP BY:

drop table if exists recipes;
create table recipes 
(id integer
,name varchar(255)
,cat varchar(255)
,instructions text
);
insert into recipes values
(1,'Tacos','Mex.','Mix and Serve'),
(2,'Kabobs','Beef','Combine/Grill');

drop table if exists ingredients;
create table ingredients
(id integer,
recipe integer,
qty integer,
measurement varchar(255),
ingredient varchar(255)
);
insert into ingredients values 
( 1,'1','1','lb','Beef' ),
( 2,'1','1','md','Onion' ),
( 3,'2','1','lb','Beef' );

select r.id, r.name, i.recipe, r.cat, r.instructions, group_concat(distinct ingredient) 
from recipes r, ingredients i 
where r.id=i.recipe group by recipe;
+----+--------+--------+------+---------------+-----------------------------------+
| id | name   | recipe | cat  | instructions  | group_concat(distinct ingredient) |
+----+--------+--------+------+---------------+-----------------------------------+
|  1 | Tacos  |      1 | Mex. | Mix and Serve | Beef,Onion                        |
|  2 | Kabobs |      2 | Beef | Combine/Grill | Beef                              |
+----+--------+--------+------+---------------+-----------------------------------+