Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Unanswered: search in fulltextindexes for multiple searchterms in multiple columns

    I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
    I don't want only the records with columns that contains both searchterms.
    I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.

    For example I search for NETWORK and PERFORMANCE in two columns.
    Jobdescr_________________________|Jobtext
    Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
    Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.

    I only get the first record because JobDescr contains both searchterms
    I don't get the second record because none of the columns contains both searchterms

    I managed to find a workaround:

    SELECT T3.jobid, T3.jobdescr
    FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
    INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
    INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
    It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.

    Does anyone know a better solution?

    Thanks in advance Bart Rouw

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You could check out Containstable, which will eliminate your need for subqueries.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    I tried but the statement is very database intensive,
    The following happens:
    1. First I make a subset with all records that contains the searchterm network in a column
    2. Second I make a subset with all records that contains the searchterm performance in a column
    3. I combine these subsets with a inner join
    4. I combine this subset with the table job to retrvieve JobId and JobDescr

    SELECT JobId, JobDescr
    FROM (
    select JobId Jobid1
    FROM containstable(dba.Job, JobDescr, '"network*"' ) FT1
    FULL JOIN containstable(dba.Job, Jobtext, '"network*"' ) FT2 ON FT1.[KEY] = FT2.[KEY]
    INNER JOIN dba.Job ON dba.Job.JobId = FT1.[KEY] OR dba.Job.JobId = FT2.[KEY]
    ) FT10
    INNER JOIN (
    select JobId Jobid2
    FROM containstable(dba.Job, JobDescr, '"performance*"' ) FT3
    FULL JOIN containstable(dba.Job, Jobtext, '"performance*"' ) FT4 ON FT4.[KEY] = FT3.[KEY]
    INNER JOIN dba.Job ON dba.Job.JobId = FT3.[KEY] OR dba.Job.JobId = FT4.[KEY]
    ) FT20 ON FT20.Jobid2 = FT10.Jobid1
    INNER JOIN dba.Job ON dba.Job.JobId = FT10.JobId1 OR dba.Job.JobId = FT20.JobId2
    Last edited by B R T; 03-23-07 at 09:40.

Posting Permissions

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