SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    St Petersburg, Russia
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    searching by two keywords

    I have a very common task. I have a table containing strings. I want to find strings that contain both of two words. So I should create a table containing rows with words and references to all strings that contain them. Such table will be exelent to make search by one keyword. But if I am to search by two keywords I am unsure what way to choose.

    1. Create the table with pairs keyword-reference. and then:
    "select c.string from keywords a, keywords b, strings c where a.word = 'word1' and b.word = 'word2' and a.refrence = c.reference" and b.refrence = c.reference"

    2. Create the table where keyword enters only once and the row contains all references for the given keyword, i.e. keword-<comma-separated list of references>. After that search by first keyword:
    "select b.word from keywords a, strings b where a.word = 'word1' and a.refrence = b.reference"
    after that go through the results and select the strings that contain the second keyword.

    I am unsure what way is better. It certain however that the more the number of keywords the better is second method.

    Also, maybe somebody can suggest another method.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use the first method to store your keywords

    (any time you store a comma-separated list inside a column, you are asking for a world of hurt)

    Code:
    select string 
      from strings
    inner
      join keywords
        on strings.reference
         = keywords.refrence
     where word in ('word1','word2')
    group
        by string
    having count(*) = 2
    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
  •