CODEWITHSUNDEEP
mysqlsqlarraysstringselect
Chris
Chris

Reputation: 13

SQL: how to select rows where id is in comma separated string?

I think i did a big mistake and i can't figure how to do this thing:

I have a table with a list of rooms and the column members (varchar) containing the list of accepted memberd id separated by commas (Es. 1,2,15,1000)

I need to select all rooms where id IN members string. Any idea??? Something like

SELECT * FROM rooms WHERE id IN rooms.members

(I know it wont work, but i hope i explained myself ^^' )

EDIT I need to do a sql query with PDO in PHP, I'm using MySQL and the goal is to get a list of all the rows where the id is listed in the members list string

Upvotes: 1

Views: 2164

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

In MySQL you can use Find_in_set() to get your job done:

DB-Fiddle:

 create table test (id int, studentIDs  varchar(50));
 insert into test values(1,'1,2,3');
 create table student (id int, name  varchar(50));
 insert into student values(1,'A');
 insert into student values(2,'B');

Query:

 select * from student where find_in_set(id,(select studentids from test))

Output:

id name
1 A
2 B

db<>fiddle here

If you are using sql server 2016 or above then can use string_split()

DB-Fiddle:

 create table test (id int, studentIDs  varchar(50));
 insert into test values(1,'1,2,3');
 create table student (id int, name  varchar(50));
 insert into student values(1,'A');
 insert into student values(2,'B');

Query:

 select * from student where id in (select trim(value) from test cross apply string_split(studentids,','))

Output:

id name
1 A
2 B

db<>fiddle here

Upvotes: 2

Related Questions