-2

In my mysql dababase i have 2 tables "brands" and "models"

CREATE table brands (
id int(11),
brand_name varchar(20));

CREATE TABLE models (
id int(11),
idBrand int(11),
model_name varchar(20));

I want to write a functions allowing me to display a result of requet like this :

Brand_name      model_name
brand_1         model_1_1, model_1_2, model_l_3
brand_2         model_2_1, model_2_2, model_2_3  
VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Why you need in ***function***? – Akina Jan 27 '20 at 10:21
  • 1
    What's your exact question? Have you tried anything so far which is not working yet? Then please share sample input data, the matching expected data, and your attempts – Nico Haase Jan 27 '20 at 11:42

2 Answers2

2

You can use group_concat function:

select b.id, max(brand_name), group_concat(model_name)
from brands b join models m
on b.id = m.idBrand
group by b.id;

Or if you want not to select id, this is also valid:

select brand_name, group_concat(model_name)
from brands b join models m
on b.id = m.idBrand
group by brand_name;

Here is a demo

If you want to have a whole set returnd then you can create procedure:

CREATE procedure test_proc ()

BEGIN

 select brand_name, group_concat(model_name) model_name
 from brands b join models m
 on b.id = m.idBrand
 group by brand_name;

END

And call it like this:

call test_proc();

Because as you can see here: https://dev.mysql.com/doc/refman/8.0/en/create-function-udf.html functions can not return this kind of data...

VBoka
  • 8,995
  • 3
  • 16
  • 24
1

You can get the desired results by using group_concat Mysql Function as below:

Select br.brand_name, 
group_concat(mod.model_name SEPARATOR ',') AS model_name
from brands br join models mod
on br.id = mod.idBrand
group by br.brand_name; 

I hope this helps!

Ranvir
  • 181
  • 1
  • 8