7. března 2012 10:09
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...
- Přesunutý Stephanie LvModerator 9. března 2012 12:40 (From:SQL Server Database Engine)
7. března 2012 11:33
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
- Upravený Ali Hamdar 7. března 2012 11:33
7. března 2012 12:26
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.
5. prosince 2012 13:22
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
5. prosince 2012 15:39
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.
- Upravený Russell FieldsMVP 5. prosince 2012 21:48 spelling