Solved

Full-text search on multiple tables in a database(MS SQL Server 2000)?

Posted on 2004-08-01
7
357 Views
Last Modified: 2008-02-07
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='B001', BranchName='California') and works as Designation(DesginationID='D0001', DesignationName='Administrator').
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?
0
Comment
Question by:tinh911
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>It seems that full-text index created on only one table.<<
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')
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
You can create full-text indexes for more than one table.

Bob
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
>>You can create full-text indexes for more than one table.<<
Absolutely.  Perhaps I misunderstood the question.

In any case, there is an error in my query, it should read:
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')
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tinh911
Comment Utility
>>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!
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>It seems that we have to create full-text search index in both Branch_Office and Designation table. Right?<<
Yes, that is correct.
0
 

Author Comment

by:tinh911
Comment Utility
Thank you, all my experts!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now