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

Great!

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