Full text CONTAINSTABLE multiple columns

Question

Hi,

I have a full text search on a table with files, documents and pictures and so on. Full text inside documents is working fine.

The problem is, I have a subject column and a keyword column that also needs to be search, because of the pictures.

Trouble is, if lets say the document it self contains the text: "yellow house" and the subject contains "story".

If I search for ""yellow house" and story" it does not return the result, if I search the two terms individually is does return.

This is my query:

SELECT K.RANK, S.SolutionID, S.Subject, S.Keywords, S.URL, S.Filename, S.Type, S.Doc FROM Solutions S
INNER JOIN
     CONTAINSTABLE(Solutions, (Subject, Keywords, Doc), @Search) AS K
     ON S.SolutionID = K.[KEY]
ORDER BY K.RANK DESC

How can I sort of AND between the columns, they should be treated as one and not individuals, is this possible? The Doc column is binary, so I can't just make one column that combines the three...

Thanks, Jesper

Answer 1

You can do this with two inner joins

SELECT K.RANK, S.SolutionID, S.Subject, S.Keywords, S.URL, S.Filename, S.Type, S.Doc FROM Solutions S
INNER JOIN
     CONTAINSTABLE(Solutions, (Subject, Keywords), @Search) AS K
     ON S.SolutionID = K.[KEY]
INNER JOIN
     CONTAINSTABLE(Solutions, (Doc), @Search) AS K2
     ON S.SolutionID = K2.[KEY]

ORDER BY K.RANK DESC

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)

Answer 2

Thank you for your quick reply, but that doesn't work as desired.

"yellow house" is only contained in the Doc column, and "story" is only contained in the Subject column.

So when searching for ""yellow house" and story" first join does not produce a hit because "yellow story" is not contained in any of those columns, second join does not produce any hit either because "story" is contained in the Doc column, so not hit.

But I want a hit because "yellow hours" is on Doc column and "story" is in Subject column.

Any suggestions?

Answer 3

You can use a union like this:

SELECT SUM(t.RANK), S.SolutionID FROM 
   SELECT * FROM CONTAINSTABLE(Solutions, (Subject, Keywords), @Search)
   UNION ALL
   SELECT * FROM CONTAINSTABLE(Solutions, Doc, @Search)
) t
INNER JOIN Solutions S ON S.SolutionID = t.[KEY]
GROUP BY S.SolutionID 
ORDER BY SUM(t.RANK) DESC

---

George Kosmidis

Answer 4

Although many columns can be included in a single full text query, the matching is always done within a column.  So JOIN or UNION will not give you exactly what you are after.  It is up to you to decide if one of these approaches is "close enough".

It sounds like you want, in essence, to query all the columns in a table as if they were only one column.  This is possible to do, but it requires some setup since you need to bring the data together in a single column.  Obviously you can write a routine to create a concatenated column, however an INDEXED VIEW would be self maintaining as would be a PERSISTED COMPUTED COLUMN.

If an indexed view would serve your purpose, then create the view something like this.

CREATE VIEW dbo.MyBigText AS SELECT RowID, Col1+Col2+Col3 AS BigColumn FROM dbo.MyTable;

CREATE UNIQUE CLUSTERED INDEX mbt_UIX ON dbo.MyTable(RowID);

Then query the MyBigText indexed view using BigColumn.

RLF