sql query where varchar field contains row from another table

[seany123]

Hello,

Im not sure if the title makes sense or not but ill try explain it best i can.

i have 2 tables,

 

the first table which ill call "haystack" contains a varchar field called keywords which contains keywords separated with a comma delimiter eg: "keyword1, keyword5, keyword2, keyword10"

the second table which ill call "needle" has for example 10 rows with a varchar called keyword, this contain only 1 of the keywords for each row eg "keyword5"

 

the aim is to run a search on the haystack table to only return rows where keywords from the needle table match.

 

so i could do this in php by exploding the haystack keywords field and then looping through to compare against the needle keywords, but that would require to select * rows and would make its take a long time, so i was hoping this could be done in sql?

 

thanks

sean

Edited January 12, 2017 by seany123

[Jacques1]

No, there is no sane way to work with the data structure you have.

 

You need to learn the basics of normalization and then fix your tables accordingly. For example, values in relational databases are supposed to be atomic, that is, comma-separated lists are almost always wrong. What you do instead is store each keyword individually. Then you can easily search them without any explode() hacks.

[benanamen]

Aside from what @Jaques1 said, when you start numbering things something1, something2 etc. you are doing something wrong.

[seany123]

No, there is no sane way to work with the data structure you have.

 

You need to learn the basics of normalization and then fix your tables accordingly. For example, values in relational databases are supposed to be atomic, that is, comma-separated lists are almost always wrong. What you do instead is store each keyword individually. Then you can easily search them without any explode() hacks.

 

Thanks for your answer, I explained the way above as i thought that was the easiest way to explain, but basically the comma delimiter keywords are a set of keywords which could be 50+ long so to create a column for each keyword wouldn't really be feasible either.  

 

Aside from what @Jaques1 said, when you start numbering things something1, something2 etc. you are doing something wrong.

Thanks for your reply, I just used that as an example.

[benanamen]

If you did what @Jaques1 said you wouldn't have answered what you did. Go learn Database Normalization, ...then come back if you have problems after updating your DB.

Edited January 12, 2017 by benanamen

[Jacques1]

Thanks for your answer, I explained the way above as i thought that was the easiest way to explain, but basically the comma delimiter keywords are a set of keywords which could be 50+ long so to create a column for each keyword wouldn't really be feasible either.

 

Who said anything about columns? I'm talking about rows. If, for example, you want to assign keywords to articles, you'd create a table article_keywords with an article_id column and a keyword column:

 article_id | keyword
------------+-------------
 1          | programming
 1          | php
 1          | sql
 2          | javascript
 2          | html

(You can also store the keywords in an extra column so that they can be reused)

 

Now it's trivial to look up the articles belonging to one or many keywords. Your “needle” table seems a bit odd, though. I would expect the keywords to come directly from the user input.

Edited January 13, 2017 by Jacques1