0

So I have two tables one is questions it has a description field that I am trying to match with the topics table search_keyword field.

For example the question table description field has this:

q.description 
-------------
Have you ever traveled to China?

And the search_keyword field on topics table has this:

t.search_keywords
-------------
travel, traveled, traveling, traveler, travel agency, jet set

So basically I am trying to narrow down a result set for questions based on the description having any of the keywords.

Here is my query so far, I am given the topic URL which is a field that narrows down things a bit, but then have to narrow down even more with matching the description to the search_keywords.

SELECT * FROM question q 
         LEFT JOIN topic t ON t.category_id = q.category_id 
         WHERE t.url = 'travel' AND 
         FIND_IN_SET(q.description, t.search_keywords)

NOTE: I can't change the database unfortunately

Any help would be appreciated!

Martin
  • 22,212
  • 11
  • 70
  • 132
JC Lopez
  • 243
  • 1
  • 2
  • 12

1 Answers1

0

You should store the keywords as separate rows instead of comma separated values:

t.search_keywords
-------------
travel
traveled
traveling
traveler
travel agency
jet set

Then you can simply add % to both sides to find matches:

select *
from question q
left join topic t on t.category_id = q.category_id
where t.url = 'travel'
    and q.description like concat('%', t.search_keywords, '%');
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • i don't have that option, the database can't change unfortunately. If you could modify answer for comma that would be great thanks. – JC Lopez May 05 '17 at 17:03
  • @JCLopez - This is the reason why normalization is so important.. You can follow [this answer](http://stackoverflow.com/a/17942691/6348498) to convert CSV values into separate rows before joining. – Gurwinder Singh May 05 '17 at 17:05
  • i agree but its a stubborn client that doesn't want to change it. I saw the CSV values solution but doesn't seem to work on fiddle. I am trying to use RLIKE worst case I will have to use PHP to do this narrowing down of questions. – JC Lopez May 05 '17 at 17:11
  • 1
    @JCLopez stubbon clients need practicalities explained to them, and a 200% charge increase to account for the excess stress they cause. – Martin May 05 '17 at 17:18
  • @Martin agreed! How can I upvote your comment?! It would so much better to redo their database it was created like 10 years ago by newbies it looks like. – JC Lopez May 05 '17 at 17:30
  • 1
    @JCLopez *"Good morning. Your database and datasets where previously built by idiots, and I would like to restart your service with a proper build for XXX amount. Alternatively, if you'd like me to continue to use this unfit for purpose database build, I will need to charge you +250% extra for Stress Management Reasons. I'm sure you Understand."* – Martin May 05 '17 at 17:53