Home > mysql, Programming > MySQL: select data whose certain column contains the specified values only

MySQL: select data whose certain column contains the specified values only

This problem could be best described by an example as below:

select * from mytbl;
+------+------+
| k    | v    |
+------+------+
| A    | 1    |
| A    | 2    |
| A    | 3    |
| B    | 1    |
| B    | 3    |
| C    | 1    |
| C    | 3    |
| C    | 4    |
| E    | 2    |
| E    | 1    |
| F    | 5    |
+------+------+

k, v are both char(10), not null.

I want to find out records whose v column is either 1 or 3 [and only 1 or 3, therefore in the above example the only qualified records are (‘B’, ‘1’) and (‘B’, ‘3’)]. This is just a simplified version of the problem I faced at work recently and I did find someone posting the similar question on Internet but I forgot to bookmark the url and I didn’t find its solutions interesting hence I pulled my hair a bit and came up with the following solution using group_concat and regexp:

select k, group_concat(distinct v order by v) as g from mytbl group by k having g regexp '^(1,?)?(3)?$';
+------+------+
| k    | g    |
+------+------+
| B    | 1,3  |
+------+------+

If the requirement becomes v is either 1, 3 or 4 (again, 1, 3 or 4 only),

select k, group_concat(distinct v order by v) as g from mytbl group by k having g regexp '^(1,?)?(3,?)?(4)?$';
+------+-------+
| k    | g     |
+------+-------+
| B    | 1,3   |
| C    | 1,3,4 |
+------+-------+
Advertisements
Categories: mysql, Programming
  1. Eric
    December 23, 2011 at 6:16 am

    Great!

  2. December 26, 2011 at 8:22 am

    I truly prize your piece of function, Excellent post. 916956

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: