Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andywakeling
Starting Member
3 Posts |
 Posted - 2008-03-07 : 07:10:41
|
| Hi all,I was tasked to implement a complex query that, amongst a whole host of other criteria, used a full text search on two columns: a summary and a description column. Lets say the search term was 'dogs cats'. The results were to be ranked in the following order:1st. Where summary column contains 'dogs' and 'cats'2nd. Where summary column contains 'dogs' or 'cats'3rd. Where description column contains 'dogs' and 'cats'4th. Where description column contains 'dogs' or 'cats'I ended up creating a temp table with its own IDENTITY column (id) and then populating it with the results of four searches as follows:1. INSERT INTO #tempTable SELECT DISTINCT [some columns], Key_Table.RANK FROM [Table]INNER JOIN CONTAINSTABLE( [Table], summary, 'dogs near cats' ) ... ORDER BY Key_Table.RANK DESC 2. INSERT INTO #tempTable SELECT DISTINCT [some columns], Key_Table.RANK FROM [Table]INNER JOIN CONTAINSTABLE( [Table], summary, 'dogs or cats' ) ... LEFT JOIN #tempTable ON [Table].[primary_key_id] = #tempTable.record_idWHERE #tempTable.record_id is NULLORDER BY Key_Table.RANK DESC 3. The same as 2 but for description column on 'dogs near cats'4. The same as 2 but for description column on 'dogs or cats'Finally i just got the whole lot back out:SELECT * FROM #tempTable ORDER BY id (the IDENTITY column)It works a treat but I'm wondering if there is a way of making something like this more concise and ideally doing it in less than four searches.Any ideas?CheersAndy |
|
|
|
|
|
Complex ranked CONTAINSTABLE searching