Search for Multiple keywords in multiple fields

Discussion in 'ASP General' started by JP SIngh, Jan 23, 2006.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    This is a complicated one, not for the faint hearted :) :) :)

    Please help if you can how to achieve this search.

    We have a freetext search entry box to allow users to search the database. I
    am searching two tables.

    SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
    TapeRecords.ItemYear, TapeRecords.TapeNumber FROM TapeRecords WHERE
    TapeRecords.Id In (select tapenumber from TapeLogDetails where
    TapeLogDetails.Description LIKE '%%asia%%' ) OR (TapeRecords.ItemTitle LIKE
    '%%asia%%' ) OR (TapeRecords.Location LIKE '%%asia%%') OR
    (TapeRecords.Country LIKE '%%asia%%') OR (TapeRecords.Keywords LIKE
    '%%asia%%') ORDER BY TapeRecords.Id DESC;

    The search logic also needs to allow users to search for multiple entries

    for example if I was to type Asia Burma

    it should find all records where asia and burma appears. Sound simple , well
    it is not.

    Because my researchers requires the search to work where the either of the
    two words (i.e. Asia and Burma) were present in any combination of the
    fields.

    i.e Asia might be in description and Burma might be in any of the other
    fields, i.e. to find the two keywords in any combination.

    can anyone help?
     
    JP SIngh, Jan 23, 2006
    #1
    1. Advertising

  2. JP SIngh

    dNagel Guest

    Without reading and digesting everything you've written, couldn't you
    use a Union to simplify the logic?

    Select [blah, blah,blah] from [TapeRecords] Where [Somecondition]
    UNION
    Select [blah, blah,blah] from [TapeRecords] Where [SomeOthercondition]
    UNION
    Select [blah, blah,blah] from [TapeRecords] Where [YetAnothercondition]

    D?

    JP SIngh wrote:
    > Hi All
    >
    > This is a complicated one, not for the faint hearted :) :) :)
    >
    > Please help if you can how to achieve this search.
    >
    > We have a freetext search entry box to allow users to search the database. I
    > am searching two tables.
    >
    > SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
    > TapeRecords.ItemYear, TapeRecords.TapeNumber FROM TapeRecords WHERE
    > TapeRecords.Id In (select tapenumber from TapeLogDetails where
    > TapeLogDetails.Description LIKE '%%asia%%' ) OR (TapeRecords.ItemTitle LIKE
    > '%%asia%%' ) OR (TapeRecords.Location LIKE '%%asia%%') OR
    > (TapeRecords.Country LIKE '%%asia%%') OR (TapeRecords.Keywords LIKE
    > '%%asia%%') ORDER BY TapeRecords.Id DESC;
    >
    > The search logic also needs to allow users to search for multiple entries
    >
    > for example if I was to type Asia Burma
    >
    > it should find all records where asia and burma appears. Sound simple , well
    > it is not.
    >
    > Because my researchers requires the search to work where the either of the
    > two words (i.e. Asia and Burma) were present in any combination of the
    > fields.
    >
    > i.e Asia might be in description and Burma might be in any of the other
    > fields, i.e. to find the two keywords in any combination.
    >
    > can anyone help?
    >
    >
     
    dNagel, Jan 23, 2006
    #2
    1. Advertising

  3. JP SIngh

    dNagel Guest

    I meant to change the table name each tie but I forgot... D.


    dNagel wrote:
    > Without reading and digesting everything you've written, couldn't you
    > use a Union to simplify the logic?
    >
    > Select [blah, blah,blah] from [TapeRecords] Where [Somecondition]
    > UNION
    > Select [blah, blah,blah] from [TapeRecords] Where [SomeOthercondition]
    > UNION
    > Select [blah, blah,blah] from [TapeRecords] Where [YetAnothercondition]
    >
    > D?
    >
    > JP SIngh wrote:
    >
    >> Hi All
    >>
    >> This is a complicated one, not for the faint hearted :) :) :)
    >>
    >> Please help if you can how to achieve this search.
    >>
    >> We have a freetext search entry box to allow users to search the
    >> database. I am searching two tables.
    >>
    >> SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
    >> TapeRecords.ItemYear, TapeRecords.TapeNumber FROM TapeRecords WHERE
    >> TapeRecords.Id In (select tapenumber from TapeLogDetails where
    >> TapeLogDetails.Description LIKE '%%asia%%' ) OR (TapeRecords.ItemTitle
    >> LIKE '%%asia%%' ) OR (TapeRecords.Location LIKE '%%asia%%') OR
    >> (TapeRecords.Country LIKE '%%asia%%') OR (TapeRecords.Keywords LIKE
    >> '%%asia%%') ORDER BY TapeRecords.Id DESC;
    >>
    >> The search logic also needs to allow users to search for multiple entries
    >>
    >> for example if I was to type Asia Burma
    >>
    >> it should find all records where asia and burma appears. Sound simple
    >> , well it is not.
    >>
    >> Because my researchers requires the search to work where the either of
    >> the two words (i.e. Asia and Burma) were present in any combination of
    >> the fields.
    >>
    >> i.e Asia might be in description and Burma might be in any of the
    >> other fields, i.e. to find the two keywords in any combination.
    >>
    >> can anyone help?
    >>
    >>
     
    dNagel, Jan 23, 2006
    #3
  4. JP SIngh

    Guest

    , Jan 23, 2006
    #4
  5. JP SIngh

    JP SIngh Guest

    Paul

    Thanks for this but this the simplest example. What I trying to work out is
    much complicated.

    Here are my tables if any of your wizards can crack

    TABLE - TapeRecords

    Id (primary key)
    ItemTitle
    Location

    TABLE - TapeLogDetails

    TapeNumber (foreign key
    Description

    If the user enters Asia Burma I split the phrase into two keywords using
    array.

    For the above example I want to return the record where both the search
    terms appear in the Item Title but also want to display the record where
    "Burma" appears in Title and "Asia" appear in any of the other fields
    including TapeLogDetails.Description.

    Please help if you can



    <> wrote in message
    news:...
    > And for some related articles that might help:
    >
    > Classic ASP Design Tips - Search Criteria on Multiple Fields
    > http://www.bullschmidt.com/devtip-searchcriteria.asp
    >
    > Classic ASP Design Tips - Search For Keywords on Multiple Fields
    > http://www.bullschmidt.com/devtip-searchmultiplefields.asp
    >
    > Best regards,
    > -Paul
    > www.Bullschmidt.com - Freelance Web and Database Developer
    > www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips
    >
     
    JP SIngh, Jan 24, 2006
    #5
  6. JP SIngh wrote:
    > Hi All
    >
    > This is a complicated one, not for the faint hearted :) :) :)
    >
    > Please help if you can how to achieve this search.
    >
    > We have a freetext search entry box to allow users to search the
    > database.


    What database?

    > I am searching two tables.
    >
    > SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
    > TapeRecords.ItemYear, TapeRecords.TapeNumber FROM TapeRecords WHERE
    > TapeRecords.Id In (select tapenumber from TapeLogDetails where
    > TapeLogDetails.Description LIKE '%%asia%%' ) OR
    > (TapeRecords.ItemTitle LIKE '%%asia%%' ) OR (TapeRecords.Location
    > LIKE '%%asia%%') OR (TapeRecords.Country LIKE '%%asia%%') OR
    > (TapeRecords.Keywords LIKE '%%asia%%') ORDER BY TapeRecords.Id DESC;
    >
    > The search logic also needs to allow users to search for multiple
    > entries
    >
    > for example if I was to type Asia Burma
    >
    > it should find all records where asia and burma appears. Sound simple
    > , well it is not.
    >
    > Because my researchers requires the search to work where the either
    > of the two words (i.e. Asia and Burma) were present in any
    > combination of the fields.
    >
    > i.e Asia might be in description and Burma might be in any of the
    > other fields, i.e. to find the two keywords in any combination.
    >
    > can anyone help?


    It sounds as if you need a full-text index. If you are using SQL Server,
    this is built in and i suggest you go to microsoft.public.sqlserver.fulltext
    for help with this.

    If you are using Access, then my first suggestion is to migrate to SQL
    Server so you can use full-text indexing. If that's not possible, then you
    are going to need to roll your own ... trust me, this is not a task for the
    faint-hearted.
    The task (which I have never done so I cannot provide any details) involves
    creating a separate table containing the record ID's and the keywords
    contained in the records (you may also need to store the field names from
    which the keywords came so that you can narrow down your search to specific
    fields). Something like this:

    TapeRecordIndex:
    Id
    Keyword
    Fieldname

    All three fields should be combined into a unique index.

    You will also need a table containing "nuisance" words, i.e., words to be
    avoided when generating the index. Words such as "and", "the", etc.

    You will need an offline scheduled task to periodically generate/refresh the
    index. The program will go through each record, looping through the fields
    and splitting the text contained in the fields into individual keywords,
    generating individual index records containing the index entries (idnoring
    the list of nuisance words). For example, if you had these records

    TapeRecord
    1 Mission to Burma Asia

    TapeLogDetails
    1 Sample description of the Mission to Burma tape

    You would wind up with:

    TapeRecordIndex
    1 Mission Title
    1 Burma Title
    1 Asia Location
    1 Sample Description
    etc.

    This allows:
    SELECT Id From TapeRecordIndex
    WHERE keyword in (Burma, Asia)

    This can be joined to the source tables to extract the source data.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jan 24, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Search using multiple keywords

    David Lozzi, Jun 2, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    529
    David Lozzi
    Jun 2, 2005
  2. savvy
    Replies:
    4
    Views:
    950
    savvy
    Nov 25, 2005
  3. David
    Replies:
    1
    Views:
    330
    Toby A Inkster
    Apr 29, 2007
  4. Andrey Hristoliubov
    Replies:
    4
    Views:
    388
    James Kanze
    Oct 1, 2008
  5. Ahmet Kilic
    Replies:
    4
    Views:
    112
    Ahmet Kilic
    Aug 27, 2009
Loading...

Share This Page