SQL Server 2005 full-text search across multiple tables and columns

Question

I'm looking for a good solution to use the pent-up SQL Serve r2005 function effectively. I currently have, for example. Employee table and address.

-Employee
Id
Name

-Address
Id
Street
City
EmployeeId

Now the user can enter search terms in only one text field, and I want these conditions to be separated and executed using the AND operator. FREETEXTTABLE works with OR automatically.

Now let's say that the user is logged in to "John Hamburg". This means that he wants to find John in Hamburg. So this is John and Hamburg.

Thus, the following will not contain any results, since CONTAINSTABLE checks each column for "John AND Hamburg".

, : /?

SELECT *
FROM Employee emp
    INNER JOIN 
        CONTAINSTABLE(Employee, *, '(JOHN  AND Hamburg)', 1000) AS keyTblSp
        ON sp.ServiceProviderId = keyTblSp.[KEY]    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = emp.EmployeeId
UNION ALL
SELECT *
FROM Employee emp 
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = emp.EmployeeId
    INNER JOIN 
        CONTAINSTABLE([Address], *, '(JOHN  AND Hamburg)', 1000) AS keyTblAddr
        ON addr.AddressId = keyTblAddr.[KEY]    

...

Answer 1

. , :

, . , , . ,

SELECT emp.*, addr.*, ISNULL(emp.Name,'') + ' ' + ISNULL(addr.City, '') AS SearchResult 
FROM Employee emp 
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = emp.EmployeeId

SearchResult.

SELECT *
FROM vEmpAddr ea
INNER JOIN CONTAINSTABLE(vEmpAddr, *, 'John AND Hamburg') a ON ea.ID = a.[Key]

Answer 2

. ?

• " " ?
• " -"?
• "", "-" ?
• "", ""?

, , , - , .

, :

2 :

1, 2, 1, 2. , .

3 :

1, 2, 3, 1, 2, 3. , .

....

, , . , " , "

EDIT:

- . , , :

insert into Employee (id, [name]) values (1, 'John Hamburg')
insert into Employee (id, [name]) values (2, 'John Smith')
insert into Employee (id, [name]) values (3, 'Bob Hamburg')
insert into Employee (id, [name]) values (4, 'Bob Smith')
insert into Employee (id, [name]) values (5, 'John Doe')

insert into Address (id, street, city, employeeid) values (1, 'Main St.', 'Springville', 1)
insert into Address (id, street, city, employeeid) values (2, 'Hamburg St.', 'Springville', 2)
insert into Address (id, street, city, employeeid) values (3, 'St. John Ave.', 'Springville', 3)
insert into Address (id, street, city, employeeid) values (4, '5th Ave.', 'Hamburg', 4)
insert into Address (id, street, city, employeeid) values (5, 'Oak Lane', 'Hamburg', 5)

, , , , , OR , UNION - , .

SELECT Id, [Name], Street, City, SUM([Rank])
FROM
(
    SELECT emp.Id, [Name], Street, City, [Rank]
    FROM Employee emp 
    JOIN [Address] addr ON emp.Id = addr.EmployeeId
    JOIN CONTAINSTABLE(Employee, *, 'JOHN OR Hamburg') AS keyTblEmp ON emp.Id = keyTblEmp.[KEY]

    UNION ALL

    SELECT emp.Id, [Name], Street, City, [Rank]
    FROM Employee emp 
    JOIN [Address] addr ON emp.Id = addr.EmployeeId
    JOIN CONTAINSTABLE([Address], *, 'JOHN OR Hamburg') AS keyTblAdd ON addr.Id = keyTblAdd.[KEY]   
) as tmp

GROUP BY Id, [Name], Street, City
ORDER BY SUM([Rank]) DESC

, ( , ):

Id       Name              Street            City           Rank
2        John Smith        Hamburg St.       Springville    112
3        Bob Hamburg       St. John Ave.     Springville    112
5        John Doe          Oak Lane          Hamburg        96
1        John Hamburg      Main St.          Springville    48
4        Bob Smith         5th Ave.          Hamburg        48

, , SQL, " " .