CODEWITHSUNDEEP
mysqlsql
Paullo
Paullo

Reputation: 2127

How can I use comma separated string from a column as value for MySQL IN statement

I have two table which I want to left join. 

DROP TABLE IF EXISTS test_1;
CREATE TABLE test_1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  description varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ;

-- ----------------------------
-- Records of test_1
-- ----------------------------
INSERT INTO test_1 VALUES ('1', 'Item A', 'Description for Item A');
INSERT INTO test_1 VALUES ('2', 'Item B', 'Description for Item B');
INSERT INTO test_1 VALUES ('3', 'Item C', 'Description for Item C');
INSERT INTO test_1 VALUES ('4', 'Item D', 'Description for Item D');
INSERT INTO test_1 VALUES ('5', 'Item E', 'Description for Item E');


DROP TABLE IF EXISTS test_2;
CREATE TABLE test_2 (
  id int(11) NOT NULL AUTO_INCREMENT,
  ids varchar(32) NOT NULL,
  PRIMARY KEY (id)
);

-- ----------------------------
-- Records of test_2
-- ----------------------------
INSERT INTO test_2 VALUES ('1', '1,2,5');

I want to return the same result that (A) will give but from (B)

(A) SELECT t1.*, t2.id as control FROM test_1 t1 LEFT JOIN test_2 t2 ON t1.id IN (1,2,5);
(B) SELECT t1.*, t2.id as control FROM test_1 t1 LEFT JOIN test_2 t2 ON t1.id IN (t2.ids)

The problem is that MySQL IN expects an array while I have a string from t2.ids column. Is there any way out?

Upvotes: 0

Views: 534

Answers (1)

Paullo
Paullo

Reputation: 2127

Thanks to all that have contributed. Nevertheless, I was able to find my solution with MySQL FIND_IN_SET

SELECT t1.*, t2.id as control FROM test_1 t1 LEFT JOIN test_2 t2 ON t1.id= FIND_IN_SET(t1.id, t2.ids);

Upvotes: 1

Related Questions