Is there any MySQL aggregation function for "CONTAINS"?

Question

Say I have this dataset

  user | group
-------- + -------
[e-mail] | A
 [e-mail] | B
 [e-mail] | A
 [e-mail] | B
 [e-mail] | A
 [e-mail] | B
 [e-mail] | C

I want to convert this to a table as follows:

  user | IN_A | IN_B | IN_C
-------- + ------- + ------- + -------
[e-mail] | TRUE | TRUE | FALSE
 [e-mail] | TRUE | FALSE | FALSE
 [e-mail] | FALSE | TRUE | FALSE
 [e-mail] | TRUE | TRUE | TRUE

I have:

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

, , , ?

Answer 1

:

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

Answer 2

, , .. GROUP_CONCAT

Answer 3

- :

SELECT distinct user,  
IF(EXISTS
    (SELECT users_a.user 
        from users as users_a 
    where users_a.group = 'A' and
        users_a.user = users.user), 
TRUE, FALSE) as IN_A,
IF(EXISTS
    (SELECT users_b.user 
        from users as users_b 
    where users_b.group = 'B' and
        users_b.user = users.user), 
TRUE, FALSE) as IN_B,
IF(EXISTS
    (SELECT users_c.user 
        from users as users_c 
    where users_c.group = 'C' and
        users_c.user = users.user), 
TRUE, FALSE) as IN_C
FROM `users` WHERE 1

!

Answer 4

You do not know how many groups you will have, but if this is the final number, I would suggest creating a column for each group or using mysql data of type SET (masking bits).

Answer 5

I'm not sure what you are doing (as I am completely new here), but if this example that you are using would not be easier if users had columns IN_A, IN_B and IN_C? Moreover, you will no longer repeat user data.