Group by equality

Question

Group by equality

Navigation

#1 by (2 votes)

3

I need to group the result when there are equal records, regardless of order, whenever the same colors are chosen - group colors and users together. I need to produce an output like the one below:

0 => array( 'cores' => 'azul, verde' , 'user' => '1, 3' )
1 => array( 'cores' => 'azul'        , 'user' => '2' )

// Usuarios 1 e 3 escolheram azul e verde
// Usuario 2 escolheu azul

My table

TABELA.ESCOLHA    TABELA.CORES
ID | USER         ID | ESCOLHA | CORES
---------         ---------------------
 1 | 1             1 | 1       | azul
 2 | 2             2 | 1       | verde
 3 | 3             3 | 2       | azul
                   4 | 3       | azul
                   5 | 3       | verde

My query

select
    group_concat( user  ) user
  , group_concat( cores ) cores
from escolha
inner join cores
        on cores.escolha = escolha.id
  group by user

I tried group by user , tried other fields, but did not work as expected.

mysql group-by

asked by anonymous 13.02.2015 / 00:38

1 answer

DOMPDF: Frame not found in cellmap. What causes this error? Get array values returned from another class

score 2 · Accepted Answer

SELECT
  group_concat(user) user,
  cores
FROM (
  SELECT
      user,
      group_concat(cores ORDER BY cores ASC) cores
  FROM
      escolha
  INNER JOIN cores ON cores.escolha = escolha.id
  GROUP BY
      user
) tmp
GROUP BY cores

link