CODEWITHSUNDEEP
phpmysqlsql
Ariel
Ariel

Reputation: 916

Concatenate MySql results

I have this query wich will search for some news and return the news data as its group, company and category. The thing is one news can have many companies and groups and i would need to concatenate this results (grupoNome and empresaNome) in one field each and so make this query return only one line:

This is my 'normal' query result:

id  titulo  categoriaId url     categoriaNome       grupoNome   empresaNome
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 4
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 4
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 4

This is my query:

SELECT n.*, nc.nome AS categoriaNome, g.nome AS grupoNome, e.nome AS empresaNome FROM `noticias` n
INNER JOIN `noticiascategorias` nc ON n.categoriaId = nc.id
INNER JOIN `noticiasgrupos` ng ON n.id = ng.noticiaId
INNER JOIN `grupos` g ON g.id = ng.grupoId
INNER JOIN `noticiasempresas` ne ON n.id = ne.noticiaId
INNER JOIN `empresas` e ON e.id = ne.empresaId
WHERE n.url LIKE 'teste';

noticiasgrupos/noticiasempresas contains the news and the group/company many to many relantionship.

grupos/empresas contains the group/company info.

EDIT:

I Would need the results to be something like:

5   Teste   1   teste   "Todas as Notícas"  "Group 1_Group2_Group_3" "Company 1_Company_Company_3"

The divisor can be anything i can use to split later (| _ *)

Upvotes: 1

Views: 78

Answers (1)

Tom
Tom

Reputation: 6663

You can use GROUP_CONCAT to do this. Just set your GROUP BY to whatever you would like to group on. You can specify the sort order and separator for the GROUP_CONCAT also.

SELECT n.*, nc.nome AS categoriaNome, 
       GROUP_CONCAT(DISTINCT g.nome ORDER BY g.nome ASC SEPARATOR '_' ) AS grupoNome,
       GROUP_CONCAT(DISTINCT e.nome ORDER BY g.nome ASC SEPARATOR '_' ) AS EmpresaNome
FROM `noticias` n
INNER JOIN `noticiascategorias` nc ON n.categoriaId = nc.id
INNER JOIN `noticiasgrupos` ng ON n.id = ng.noticiaId
INNER JOIN `grupos` g ON g.id = ng.grupoId
INNER JOIN `noticiasempresas` ne ON n.id = ne.noticiaId
INNER JOIN `empresas` e ON e.id = ne.empresaId
WHERE n.url LIKE 'teste'
GROUP BY n.id;

Upvotes: 2

Related Questions