Jump to content
Sign in to follow this  
seany123

sql query where varchar field contains row from another table

Recommended Posts

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 by seany123

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

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 by Jacques1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.