Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any MySQL Aggregate Function for "CONTAINS"?

Say I have this data set

  user  | group
--------+-------
[email protected] |   A
[email protected] |   B
[email protected] |   A
[email protected] |   B
[email protected] |   A
[email protected] |   B
[email protected] |   C

I want to convert this into a table like this:

  user  | IN_A  | IN_B  | IN_C
--------+-------+-------+-------
[email protected] | TRUE  | TRUE  | FALSE
[email protected] | TRUE  | FALSE | FALSE
[email protected] | FALSE | TRUE  | FALSE
[email protected] | TRUE  | TRUE  | TRUE

I've got:

SELECT
  user,
  IF(LOCATE('A', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_A,
  IF(LOCATE('B', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_B,
  IF(LOCATE('C', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_C
FROM users
GROUP BY user

What I'm wondering is if there is a better way to known if an aggregated field contains a value, or if this is the only way?

like image 890
Nick Avatar asked Sep 16 '10 10:09

Nick


2 Answers

The right way to do it:

SELECT
   user,
   IF(SUM(group = 'A'), TRUE, FALSE) AS IN_A,
   IF(SUM(group = 'B'), TRUE, FALSE) AS IN_B,
   IF(SUM(group = 'C'), TRUE, FALSE) AS IN_C
FROM users
GROUP BY user
like image 98
nzn Avatar answered Sep 20 '22 13:09

nzn


I guess this is the only way i.e., GROUP_CONCAT

like image 29
Faisal Feroz Avatar answered Sep 21 '22 13:09

Faisal Feroz