-1

I have two tables

Table 1

enter image description here

Table 2

enter image description here

I want to See the rows from Table 1 which column tag matches one or multiple keyword value from Table 2.

Here the Result Should be

id = 1,3,6

in id 1 both 78 and 213 are there but the row will appear 1 time.

I am tried with SELECT * FROM Table1 WHERE tag IN (SELECT keyword FROM table2). But its returning only those rows Whose Starts matches with Table 2 value like the result came only 3. Because that rows tag starts with 10.

Hritam
  • 51
  • 1
  • 1
  • 5
  • Hint: Use the `FIND_IN_SET()` function. – Tim Biegeleisen Sep 25 '19 at 09:12
  • As per my view in FIND_IN_SET() I can compare 1 value..But not the whole from another table. The purpose is different here. I am not comparing 10 with the column but comparing either 78 or 10 or 213 in tbl1. Thats why I mentioned 2 table. Not value – Hritam Sep 25 '19 at 09:24
  • I undeleted my answer, but note that you could fairly easily put together my answer using the duplicate link. – Tim Biegeleisen Sep 25 '19 at 09:24

1 Answers1

0

We can try using FIND_IN_SET here:

SELECT t1.id
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2
              WHERE FIND_IN_SET(t2.keyword, t1.tag) > 0);

Note that storing comma separated values in your database is generally not ideal, because it represents unnormalized data. Instead, get each tag value onto a separate row.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for the query. But comparing a huge data will cause a lot of time. Whatever my concern is resolved for now. – Hritam Sep 25 '19 at 09:32
  • @Hritam You basically can't use an index in my query, because the join comparison is nested inside the `FIND_IN_SET` function. If you need performance, then get CSV tag value onto a separate record, and then add an appropriate index. – Tim Biegeleisen Sep 25 '19 at 09:34