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

Question

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?

Don't store csv data in database columns. That's really bad schema design, and this is just one reason why out of many. — Joel Coehoorn
– Joel Coehoorn, Commented Aug 21, 2014 at 14:31
It's not really a CVS data rather some group of data that have reference in some other table — Paullo
– Paullo, Commented Aug 21, 2014 at 14:36
Why would it be so bad for your application's design to have a true M:N relationship table for Table2? As such: (id INT PRIMARY KEY AUTO_INCREMENT, fromID INT NOT NULL, toID INT NOT NULL) This would let you freely add any number of relationships, not have to look up one row to update a relationship set, and you can join normally and your in clause can be a simple select statement against Table2. — Jaaz Cole
– Jaaz Cole, Commented Aug 21, 2014 at 14:51
I clearly understand that all. But this is really a different case if I should show the full table design/structure — Paullo
– Paullo, Commented Aug 21, 2014 at 15:02

Paullo · Accepted Answer · 2014-08-21 15:04:36Z

1

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);

answered Aug 21, 2014 at 15:04

Paullo

2,1374 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related