MySQL query to find related rows from single table

Question

I have a table structure as attached in MySQL Database.

The problem is for id=1, I have tags like AI,BOTS in tag column. So when I query with id=1, I need to fetch all the rows which contain either AI or BOTS. So the output will be rows with id=1, id=3 and id=4. Similarly, if i query with id=4, I should get rows 1 and 4.

I tried query

Select * From news where tags in (select tags from news where id=1);

but it didnt help. I don`t have much expertise in writing too complex database queries, any help would be appreciated.

Actual values are in db, I just copied to excel to minimise the db columns and sake of simplicity — Rajendra Gupta, Mar 14 '17 at 09:17
i think, the data aren't stored in a `csv-file`. This should just represent the data easier, than watching it in MySQL — SacrumDeus, Mar 14 '17 at 09:17
I think, this question will be answered by this: [Where field contains A or B](http://stackoverflow.com/questions/6121630/how-to-write-mysql-query-where-a-contains-a-or-b) — SacrumDeus, Mar 14 '17 at 09:19
@RajendraGupta Check this link out [link](http://stackoverflow.com/questions/6121630/how-to-write-mysql-query-where-a-contains-a-or-b) — SacrumDeus, Mar 14 '17 at 09:29
@SacrumDeus We know that is not a CSV file. But what i say CSV DATA in a column of a table is very bad — Jens, Mar 14 '17 at 09:31
@SacrumDeus select * from news where tags LIKE(select tags from news where id=1); will o/p only first row — Rajendra Gupta, Mar 14 '17 at 09:39
@RajendraGupta You can do `select * from news where id = 1 and (tags = 'AI' or tags = 'BOTS');` I think, this should work — SacrumDeus, Mar 14 '17 at 09:59
@SacrumDeus where id=1 will only give row with id =1 but i also need other rows where tags="AI" or tags="BOTS" — Rajendra Gupta, Mar 14 '17 at 11:25
for this reason i add the query ` where id = 1 and (tags = 'AI' or tags = 'BOTS');` — SacrumDeus, Mar 14 '17 at 12:04

score 1 · Answer 1 · edited Sep 21 '21 at 18:24

create a new function name with find_in_set_extra

BEGIN
DECLARE limitCount INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE res INT DEFAULT 0;
DECLARE temp TEXT;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',',''));
simple_loop:LOOP
SET counter = counter + 1;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList,',',counter),',',-1);
SET res = FIND_IN_SET(temp,targetList);
IF res > 0 THEN LEAVE simple_loop; END IF;
IF counter = limitCount THEN LEAVE simple_loop; END IF;
END LOOP simple_loop;
RETURN res;
END<

using this function you can find data like

find_in_set_extra('a,b,c','b,c')

MySQL query to find related rows from single table

1 Answers1