July 1, 2003 at 5:59 am
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.
July 1, 2003 at 7:19 am
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.
July 1, 2003 at 9:54 pm
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
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