0

I have a table like this:

id  |  value1  | value2
-------------------------
1   | a,b,c,d  | a,d,e,f
-------------------------
2   | d,e,f,a  | 
-------------------------
3   | a,x,y,z  | d,e,f

How can I get all rows that have "a" in [value1] but don't have "a" in [value2]? It should be this:

2   | d,e,f,a  | 
-------------------------
3   | a,x,y,z  | d,e,f

Thanks in advance!

Nấm Lùn
  • 1,277
  • 6
  • 28
  • 48

3 Answers3

1

You should think about normalization and never store data as comma separated string. However in this case you may use find_in_set, which is not efficient in long run. So while you are normalizing data (assuming you will do it), in the meanwhile you may use the following.

mysql> select find_in_set('a','a,b,c,d') as pos ;
+-----+
| pos |
+-----+
|   1 |
+-----+
1 row in set (0.02 sec)

mysql> select find_in_set('a','d,e,f') as pos ;
+-----+
| pos |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

So the query becomes

select * from table_name
where
find_in_set('a',value1) > 0 
and find_in_set('a',value2) = 0 
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
0

Try this query:

SELECT *
FROM `table`
WHERE `value1` LIKE '%a%'
AND `value2` NOT LIKE '%a%'
Ataboy Josef
  • 2,087
  • 3
  • 22
  • 27
0

if table name is 'test'

select * from test where value1 like '%a%' and value2 not like '%a%';
iKing
  • 667
  • 10
  • 15