SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Rows where some of columnA exists in columnB

    I tried looking on the web for an answer but could not find anything so I will ask you guys. Is it possible to write a query that returns rows where at least one word in columnA is also found in columnB? Basically the keywords column shouldn't contain any words that exist in the body column. Those are the rows I'm wanting to find.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,615
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Well, depending on your data and the results you actually want, there are a couple of things you can look at.

    You may want to take a look at using JOIN in your SELECT queries.

    This article offers a pretty good explanation on what it is and how to use it: http://www.sitepoint.com/understandi...ysql-database/

    For something a bit more technical, the official documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html


    Also, you may need to look at using LIKE instead of = for string comparisons.

    http://dev.mysql.com/doc/refman/5.5/...functions.html
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So the keywords column is a comma separated value column (keyword1, keyword2, keyword3, ...) ? That's too bad. If you had a "keywords" table with one articleid/keyword pair per row (supposing your table is called "articles"), then it would be easy to do what you want.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Force Flow View Post
    You may want to take a look at using JOIN in your SELECT queries.
    except there was no hint of more than one table in this problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Correct, there is only one table involved here, the searches table. So is this kind of query even possible?
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    So is this kind of query even possible?
    yes, but it will be ugly as sin

    you'll have to hardcode as many conditions as there are keywords you want to check

    each one will use a SUBSTRING_INDEX function to pull out a keyword, and then LIKE to see if it's in the body
    Code:
    SELECT ...
      FROM searches
     WHERE body LIKE CONCAT('%',SUBSTRING_INDEX(keywords,1),'%')
        OR body LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,2),-1),'%')
        OR body LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,3),-1),'%')
        ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, this is what I got:

    Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'

    Thank you.
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'
    obviously, the sql i gave you was untested, in fact it was right off the top of my head, and i'm sorry, but i guess i mis-remembered the function syntax

    do me a favour please?

    look up SUBSTRING_INDEX in da manual and you will see immediately what's wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •