tinh911
asked on
Full-text search on multiple tables in a database(MS SQL Server 2000)?
For example, we have 3 tables : Employee, Branch_Office, Designation, and their relationships are as following:
Branch_Oficce
(
BranchID (PK)
BranchName
.....
)
Designation
(
DesignationID (PK)
DesginationName
.....
)
Employee
(
EmployeCode (PK)
BranchID (FK) (reference to Branch_Office PK)
DesignationCode (FK) (reference to Designaiton PK)
)
Assume we have an employee (for example, EmployeeCode='E0001') works in Branch_Office(BranchID='B0
My problem is that I cannot full-text search an employee base on their BranchName or DesignationName.
It seems that full-text index created on only one table.
Can I full-text search on BranchName or DesignationName of an employee in this situation?
Branch_Oficce
(
BranchID (PK)
BranchName
.....
)
Designation
(
DesignationID (PK)
DesginationName
.....
)
Employee
(
EmployeCode (PK)
BranchID (FK) (reference to Branch_Office PK)
DesignationCode (FK) (reference to Designaiton PK)
)
Assume we have an employee (for example, EmployeeCode='E0001') works in Branch_Office(BranchID='B0
My problem is that I cannot full-text search an employee base on their BranchName or DesignationName.
It seems that full-text index created on only one table.
Can I full-text search on BranchName or DesignationName of an employee in this situation?
You can create full-text indexes for more than one table.
Bob
Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>It seems that full-text index created on only one table.<<
Sorry for my bad explanation. My idea is that when we create full-text search index in Enterprise Manager, we can only choose ONE table. We can not choose more table in a full-text search index. Maybe I got confused at that time. So, forget my idea. :-).
to acperkins:
Your query:
Select *
From Employee e
Inner Join Branch_Office b On e.BanchID = b.BranchID
Inner Join Designation d on e.DesignationCode = d.DesignationID
Where Contains(b.*, 'California')
Or Contains(d.*, 'Administrator')
It seems that we have to create full-text search index in both Branch_Office and Designation table. Right?
OK, I'll try it!
Sorry for my bad explanation. My idea is that when we create full-text search index in Enterprise Manager, we can only choose ONE table. We can not choose more table in a full-text search index. Maybe I got confused at that time. So, forget my idea. :-).
to acperkins:
Your query:
Select *
From Employee e
Inner Join Branch_Office b On e.BanchID = b.BranchID
Inner Join Designation d on e.DesignationCode = d.DesignationID
Where Contains(b.*, 'California')
Or Contains(d.*, 'Administrator')
It seems that we have to create full-text search index in both Branch_Office and Designation table. Right?
OK, I'll try it!
I was only trying to say that you can repeat the operation of creating an index for each table that needs full-text searches--not having more than one table in the index.
Bob
Bob
>>It seems that we have to create full-text search index in both Branch_Office and Designation table. Right?<<
Yes, that is correct.
Yes, that is correct.
ASKER
Thank you, all my experts!
That is correct.
>>Can I full-text search on BranchName or DesignationName of an employee in this situation?<<
There is nothing to stop you joining these three tables and doing Full Text search on all tables, something like:
Select *
From Employee e
Inner Join Branch_Office b On e.BanchID = b.BranchID
Inner Join Designation d on e.DesignationCode = d.DesignationID
Where Contains(Branch_Office.*, 'California')
Or Contains(Designation.*, 'Administrator')