SQL select rows where field contains word from another table's fields

Question

Using MySQL, I have a table with a name field. I want to check that name field against a set of "keywords" that indicate that the name is not a person, but a business.

In words: Get the entire row if the name contains any of the keywords.

My attempt: SELECT * FROM leads WHERE CONTAINS(leads.name, (SELECT word FROM keywords)); (Returns "Subquery returns more than 1 row")

It is better to store keywords in a junction table rather than using a full-text index. — Gordon Linoff, Jul 06 '15 at 16:46
@GordonLinoff can you help me understand how that helps here? I'm sure it does I just can't see it. I'll be doing this operation somewhat frequently so I'd really like to know. — roundar, Jul 06 '15 at 17:29

score 3 · Accepted Answer · answered Jul 06 '15 at 16:50

3

It does not work like that. You can use a join instead

SELECT l.* 
FROM leads l
JOIN keywords k on instr(leads.name, word) > 0

answered Jul 06 '15 at 16:50

juergen d

201,996
37
293
362

instr will give false positives if you have keywords like butterfly and butter (butterfly will be returned as a match on a search for butter). – John Jul 06 '15 at 17:37
I'm not sure if it's really the best solution, but all my keywords are surrounded by spaces to avoid false positives like this. I've been handling this problem programmatically until now (I surround the name with spaces as well to make sure I catch all the words). – roundar Jul 06 '15 at 17:43

score 1 · Answer 2 · answered Jul 06 '15 at 17:45

1

Here you are:

SELECT * FROM leads WHERE leads.name IN (SELECT word FROM keywords);

Hope this helps.

answered Jul 06 '15 at 17:45

hungndv

2,121
2
19
20

John · Answer 3 · 2015-07-06T17:34:23.137

0

Something like this should do it:

SELECT distinct
    l.* 
FROM 
    leads l
    join keywords k on l.name = k.word

EDIT: or something like this if you have a comma delimited list

SELECT distinct
    l.* 
FROM 
    leads l
    join keywords k1 on l.name like concat('%,', k1.word)
    join keywords k2 on l.name like concat('%,', k2.word, ',%')
    join keywords k3 on l.name like concat(k2.word, ',%')
    join keywords k4 on l.name = k4.word

edited Jul 06 '15 at 17:34

answered Jul 06 '15 at 16:49

John

3,458
4
33
54

Thanks, the problem is `l.name` must only _contain_ `k.word`, not be _equal_ to it. – roundar Jul 06 '15 at 17:23
Added update to answer to include the case for a comma delimited list – John Jul 06 '15 at 17:34

score 0 · Answer 4 · answered Oct 21 '17 at 15:27

0

with numeric values, i use CONCAT with comma separator to be sure to find the exact value. In your case, it would be :

SELECT l.* FROM leads l 
JOIN keywords k 
on instr(CONCAT(',', leads.name, ','),  CONCAT(',', word, ',')) > 0

answered Oct 21 '17 at 15:27

Cyril Jacquart

2,632
3
25
24

SQL select rows where field contains word from another table's fields

4 Answers4

Linked