CODEWITHSUNDEEP
mysqlsqljoin
user4488093
user4488093

Reputation:

`GROUP_CONCAT()` for many to many

I have 3 tables: types, brands and type_brand.

types: id|title|slug

brands: id|title|slug

and type_brand: id|type_id|brand_id

So, I need get all records from brands table with types in result column. Like this: 

id | title | slug | types
1     tit    TIT    type1,type2,type3

I write query for this:

select brand.title, brand.slug, GROUP_CONCAT(j2.slug) AS types
  from brand brand
LEFT join type_brand j1 on j1.brand_id = brand.id
LEFT join types j2 on j2.id = j1.type_id
limit 10

It's works wrong, because get brand that only there is in type_brand table. While I need get ALL brands from table brands with optional column types in result.

Help me

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Do you just need GROUP BY?

select brand.title, brand.slug, GROUP_CONCAT(j2.slug) AS types
from brand brand LEFT join
     type_brand j1
     on j1.brand_id = brand.id LEFT join
     types j2
     on j2.id = j1.type_id
group by brand.title, brand.slug 
limit 10;

Upvotes: 3

Related Questions