How do I search for ALL words within ANY columns of multiple Full Text indexes?

Question

If I have two full text indexes on tables such as Contacts and Companies, how can I write a query that ensures ALL the words of the search phrase exist within either of the two indexes?

For example, if I'm searching for contacts where all the keywords exist in either the contact record or the company, how would I write the query?

I've tried doing CONTAINSTABLE on both the contact and company tables and then joining the tables together, but if I pass the search phrase in to each as '"searchTerm1*' AND '"searchTerm2*"' then it only matches when all the search words are on both indexes and returns too few records. If I pass it in like '"searchTerm1*' OR '"searchTerm2*"' then it matches where any (instead of all) of the search words are in either of the indexes and returns too many records.

I also tried creating an indexed view that joins contacts to companies so I could search across all the columns in one shot, but unfortunately a contact can belong to more than one company and so the ContactKey that I was going to use as the key for the view is no longer unique and so it fails to be created.

It seems like maybe I need to break the phrase apart and query for each word separately and then join the results back together to be able to ensure all the words were matched on, but I can't think of how I'd write that query.

Here's an example of what the model could look like:

Contact           CompanyContact    Company
--------------    --------------    ------------
ContactKey        ContactKey        CompanyKey
FirstName         CompanyKey        CompanyName
LastName

I have a Full Text index on FirstName,LastName and another on CompanyName.

Could you share some information about the model? So far all I understand is that you have two tables: Contacts and Companies and that each of them contain ContactKey. I'm guessing that ContactKey is not the value you're searching against. — Paurian, Apr 24 '17 at 17:15
@Paurian, please see my update and let me know if you need more info. Thanks. — adam0101, Apr 24 '17 at 17:36
@BogdanSahlean that throws an error saying CompanyContact cannot be a part of the CONTAINSTABLE query because it is not full-text indexed and I can't add a full text index to it because there's no text in that table. — adam0101, Apr 25 '17 at 19:45
@BogdanSahlean I did all that. Then when I went to query against that view using `CONTAINSTABLE` I got the error that I mentioned in my previous comment. — adam0101, Apr 25 '17 at 20:35
@BogdanSahlean it is referencing the indexed view. I assume the error is because the indexed view uses CompanyContact. — adam0101, Apr 26 '17 at 14:24

Paurian · Answer 1 · 2017-04-26T22:09:21.107

This answer is rebuilt to address your issue such that multiple strings must exist ACROSS the fields. Note the single key in the CompanyContactLink linking table:

CREATE FULLTEXT CATALOG CompanyContact WITH ACCENT_SENSITIVITY = OFF
GO

CREATE TABLE Contact ( ContactKey INT IDENTITY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL )
ALTER TABLE Contact ADD CONSTRAINT PK_Contact PRIMARY KEY NONCLUSTERED ( ContactKey )

CREATE TABLE Company ( CompanyKey INT IDENTITY, CompanyName VARCHAR(50) NOT NULL )
ALTER TABLE Company ADD CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED ( CompanyKey )
GO

CREATE TABLE CompanyContactLink ( CompanyContactKey INT IDENTITY NOT NULL, CompanyKey INT NOT NULL, ContactKey INT NOT NULL )
GO

INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Dipper', 'Pines' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Mabel', 'Pines' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Stanley', 'Pines' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Soos', 'Ramirez' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Wendy', 'Corduroy' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Sheriff', 'Blubs' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Bill', 'Cipher' )
INSERT INTO Contact ( FirstName, LastName ) VALUES ( 'Pine Dip', 'Nobody' )
INSERT INTO Contact ( FirstNAme, LastName ) VALUES ( 'Nobody', 'Pine Dip' )

INSERT INTO Company ( CompanyName ) VALUES ( 'Mystery Shack' )
INSERT INTO Company ( CompanyName ) VALUES ( 'Greesy Diner' )
INSERT INTO Company ( CompanyName ) VALUES ( 'Watertower' )
INSERT INTO Company ( CompanyName ) VALUES ( 'Manotaur Cave' )
INSERT INTO Company ( CompanyName ) VALUES ( 'Big Dipper Watering Hole' )
INSERT INTO Company ( CompanyName ) VALUES ( 'Lost Pines Dipping Pool' )
GO

INSERT INTO CompanyContactLink Values (3, 5), (1, 1), (1, 2), (1, 3), (1, 4), (1,5), (5,1), (3,1), (4,1)
GO

CREATE FULLTEXT INDEX ON Contact (LastName, FirstName)
KEY INDEX PK_Contact
ON CompanyContact
WITH STOPLIST = SYSTEM

CREATE FULLTEXT INDEX ON Company (CompanyName)
KEY INDEX PK_Company
ON CompanyContact
WITH STOPLIST = SYSTEM
GO

CREATE VIEW CompanyContactView
WITH SCHEMABINDING
AS
  SELECT
    CompanyContactKey,
    CompanyName,
    FirstName,
    LastName
  FROM
    dbo.CompanyContactLink
    INNER JOIN dbo.Company ON Company.CompanyKey = CompanyContactLink.CompanyKey
    INNER JOIN dbo.Contact ON Contact.ContactKey = CompanyContactLink.ContactKey
GO

CREATE UNIQUE CLUSTERED INDEX idx_CompanyContactView ON CompanyContactView (CompanyContactKey);
GO

CREATE FULLTEXT INDEX ON CompanyContactView (CompanyName, LastName, FirstName)
KEY INDEX idx_CompanyContactView
ON CompanyContact
WITH STOPLIST = SYSTEM
GO

-- Wait a few moments for the FULLTEXT INDEXing to take place.
-- Check to see how the index is doing ... repeat the following line until you get a zero back.

DECLARE @ReadyStatus INT
SET @ReadyStatus = 1
WHILE (@ReadyStatus != 0)
BEGIN
  SELECT @ReadyStatus = FULLTEXTCATALOGPROPERTY('CompanyContact', 'PopulateStatus')
END

SELECT
  CompanyContactView.*
FROM
  CompanyContactView
WHERE
  FREETEXT((FirstName,LastName,CompanyName), 'Dipper') AND
  FREETEXT((FirstName,LastName,CompanyName), 'Shack')
GO

And for the sake of your example with Wendy at the Watertower:

SELECT
  CompanyContactView.*
FROM
  CompanyContactView
WHERE
  FREETEXT((FirstName,LastName,CompanyName), 'Wendy') AND
  FREETEXT((FirstName,LastName,CompanyName), 'Watertower')
GO

This only searches either contacts or companies. Using your data, if I was searching for contacts and Wendy was a contact at Watertower, if I searched for `Wendy Watertower` I would expect to get back Wendy's contact key, but you don't have any relationship defined between the contact and companies. — adam0101, Apr 26 '17 at 18:41
Answer has been rebuilt to address your question; specifically, how to have multiple required search strings across multiple fields. — Paurian, Apr 26 '17 at 22:10
And to give some credit to where it's due. It so happens, after overhauling the answer, I came to realize that these changes do exactly what Bogdan Sahlean suggested. If you find this full-code solution something that's useful to your issue, please make sure to upvote his comment as well. — Paurian, Apr 26 '17 at 22:16

adam0101 · Accepted Answer · 2020-06-22T13:42:25.810

I created a method that works with any number full text indexes and columns. Using this method, it is very easy to add additional facets to search for.

Split the search phrase into rows in a temp table
Join to this temp table to search for each search term using CONTAINSTABLE on each applicable full text index.
Union the results together and get the distinct count of the search terms found.
Filter out results where the number of search terms specified does not match the number of search terms found.

Example:

DECLARE @SearchPhrase nvarchar(255) = 'John Doe'
DECLARE @Matches Table(
    MentionedKey int,
    CoreType char(1),
    Label nvarchar(1000),
    Ranking int
)

-- Split the search phrase into separate words.
DECLARE @SearchTerms TABLE (Term NVARCHAR(100), Position INT)
INSERT INTO @SearchTerms (Term, Position)
SELECT dbo.ScrubSearchTerm(Term)-- Removes invalid characters and convert the words into search tokens for Full Text searching such as '"word*"'.
FROM dbo.SplitSearchTerms(@SearchPhrase)

-- Count the search words.
DECLARE @numSearchTerms int = (SELECT COUNT(*) FROM @SearchTerms)

-- Find the matching contacts.
;WITH MatchingContacts AS
(
    SELECT
        [ContactKey] = sc.[KEY],
        [Ranking] = sc.[RANK],
        [Term] = st.Term
    FROM @SearchTerms st
    CROSS APPLY dbo.SearchContacts(st.Term) sc -- I wrap my CONTAINSTABLE query in a Sql Function for convenience
)
-- Find the matching companies
,MatchingContactCompanies AS
(
    SELECT
        c.ContactKey,
        Ranking = sc.[RANK],
        st.Term
    FROM @SearchTerms st
    CROSS APPLY dbo.SearchCompanies(st.Term) sc
    JOIN dbo.CompanyContact cc ON sc.CompanyKey = cc.CompanyKey
    JOIN dbo.Contact c ON c.ContactKey = cc.ContactKey
)
-- Find the matches where ALL search words were found.
,ContactsWithAllTerms AS
(
    SELECT
        c.ContactKey,
        Ranking = SUM(x.Ranking)
    FROM (
        SELECT ContactKey, Ranking, Term FROM MatchingContacts  UNION ALL
        SELECT ContactKey, Ranking, Term FROM MatchingContactCompanies
    ) x
    GROUP BY c.ContactKey
    HAVING COUNT(DISTINCT x.Term) = @numSearchTerms
)
SELECT
    *
FROM ContactsWithAllTerms c

Update Per the comments, here's an example of my SearchContacts function. It's just a simple wrapper function because I was using it in multiple procedures.

CREATE FUNCTION [dbo].[SearchContacts]
(
    @contactsKeyword nvarchar(4000)
)
RETURNS @returntable TABLE
(
    [KEY] int,
    [RANK] int
)
AS
BEGIN
    INSERT @returntable
    SELECT [KEY],[RANK] FROM CONTAINSTABLE(dbo.Contact, ([FullName],[LastName],[FirstName]), @contactsKeyword)
    RETURN
END
GO

Hi, I'm looking at doing something similar but confused as to what's going on with dbo.SearchContacts, can you give an example of this function please? — david-l, Jun 22 '20 at 11:56

How do I search for ALL words within ANY columns of multiple Full Text indexes?

2 Answers2