Is there any MySQL Aggregate Function for "CONTAINS"?

Question

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?

Answer 1

Not sure how many groups you will be having but if its a finiate number I would suggest that you create a column for each group or use the mysql data type SET (bit masking).

Answer 2

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

Answer 3

I'm not sure if this is the way you do things (as I'm quite new here) but if that's the example you're using wouldn't it be easier to have the columns user, IN_A, IN_B and IN_C? Especially since that way you wouldn't be repeating the user data again