Quick FullText question

  • I am just starting to look at the fulltext search facility.

    All the examples for the CONTAINS statement show a SELECT against only one table.

    Can I use CONTAINS when there are multiple tables or do I have to do a join using CONTAINSTABLE?

    Thanks in advance.

  • At the moment I only have one table with search facility. I tried a query with the table joined to itself (using id) and with multple CONTAINS statement. It worked with one CONTAINS in the where and one in the ON as well as both in the where.

    Hope this helps.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Hi David

    Really depends on your SQL and the complexity of the joins/sql. For example:

    select org_id

    from organisation as A

    inner join Organisation_Name AS B ON B.orgname_org_id=A.org_id

    inner join CONTAINSTABLE(organisation_name, *, '"abc"') as C

    on C. = B.orgname_id

    Table 'Organisation'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.

    Table 'Organisation_Name'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0.

    CPU time = 5 ms, elapsed time = 5 ms.

    Can also be written as:

    select org_id

    from organisation as A

    LEFT OUTER JOIN Organisation_Name AS B ON B.orgname_org_id=A.org_id

    where exists

    (select 'x'

    from Organisation_Name C

    where contains(*,'"abc"')

    and C.orgname_id = B.orgname_id)

    Table 'Organisation'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.

    Table 'Organisation_Name'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0.

    CPU time = 0 ms, elapsed time = 5 ms.

    There are a few other combos to consider and play with of course.

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply