MySQL Group_concat result IN given numbers

Question

I'm trying to find out, if any number in a group_concat result is in a given set of numbers.

This example data can be used to try it out:

CREATE TABLE `events` (
  `eventid` int(11) NOT NULL AUTO_INCREMENT,
  `eventname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`eventid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `events`
-- ----------------------------
BEGIN;
INSERT INTO `events` VALUES ('1', 'my event');
COMMIT;

-- ----------------------------
--  Table structure for `events2groups`
-- ----------------------------
CREATE TABLE `events2groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventid` int(11) DEFAULT NULL,
  `groupid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `events2groups`
-- ----------------------------
BEGIN;
INSERT INTO `events2groups` VALUES ('1', '1', '5'), ('2', '1', '3'), ('3', '1', '1');
COMMIT;

-- ----------------------------
--  Table structure for `groups`
-- ----------------------------
CREATE TABLE `groups` (
  `groupid` int(11) NOT NULL AUTO_INCREMENT,
  `groupname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`groupid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `groups`
-- ----------------------------
BEGIN;
INSERT INTO `groups` VALUES ('1', 'group1'), ('2', 'group2'), ('3', 'group3'), ('4', 'group4'), ('5', 'group5');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Then fire this query:

SELECT GROUP_CONCAT(groups.groupid), 
IF ( GROUP_CONCAT(groups.groupid) IN (1,2,3), 'yes', 'no' ) as isInGroup 
FROM `events`
LEFT JOIN events2groups ON events.eventid = events2groups.eventid
LEFT JOIN groups ON events2groups.groupid = groups.groupid
GROUP BY events.eventid

the first result column shows, that the event has the groups 5, 3 and 1. The user has the groups 1, 2 and 3, so the 3 and the 1 match. How can I query this to get the 'yes' from the IF?

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Mar 17 '15 at 10:14
http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists this approach might help you. — scones, Mar 17 '15 at 10:18

Gordon Linoff · Accepted Answer · 2015-03-17T11:43:51.397

1

Why use the group_concat() results? Just use conditional aggregation:

SELECT GROUP_CONCAT(groups.groupid), 
       (case when max(groups.group_id in (1, 2, 3)) > 0 then 'yes' else 'no'
        end) as isInGroup 
FROM `events` LEFT JOIN
      events2groups
      ON events.eventid = events2groups.eventid LEFT JOIN
      groups
      ON events2groups.groupid = groups.groupid
GROUP BY events.eventid;

edited Mar 17 '15 at 11:43

answered Mar 17 '15 at 10:44

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Mar 17 '15 at 10:47

SELECT GROUP_CONCAT(groups.groupid), 
  GROUP_CONCAT(case when groups.groupid IN (1,2,3) then  'yes' else 'no' end) as isInGroup 
FROM `events`
LEFT JOIN events2groups ON events.eventid = events2groups.eventid
LEFT JOIN groups ON events2groups.groupid = groups.groupid
GROUP BY events.eventid
;

result no,yes,yes

MySQL Group_concat result IN given numbers

2 Answers2