Search code examples
mysqlsqlcsvsql-like

MYSQL CSV column check for exclude

I need to find a record who dont have a specific value in CSV column. below is the table structure 

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `keywords` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample record1:  100, Sam, Thompson, "50,51,52,53"
Sample record2:  100, Wan, Thompson, "50,52,53"
Sample record3:  100, Kan, Thompson, "53,52,50"

50 = sports
51 = cricket
52 = soccer
53 = baseball

i need to find the employees name who has the tags of "sports,soccer,baseball" excluding cricket so the result should return only 2nd and 3rd record in this example as they dont have 51(cricket) but all other 3 though in diff pattern.

My query is below, but i couldnt get it worked any more.

SELECT t.first_name,FROM `User` `t` WHERE (keywords like '50,52,53') LIMIT 10

is there anything like unlike option? i am confused how to get this worked.

Solution

  • You could use FIND_IN_SET:

    SELECT t.first_name
FROM `User` `t` 
WHERE FIND_IN_SET('50', `keywords`) > 0
  AND FIND_IN_SET('52', `keywords`) > 0
  AND FIND_IN_SET('53', `keywords`) > 0
  AND FIND_IN_SET('51', `keywords`) = 0;

    Keep in mind it could be slow. The correct way is to normalize your table structure.