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 |
|
staplebottom
Starting Member
29 Posts |
 Posted - 2007-11-29 : 13:41:47
|
| If I have a table which has multiple child rows in another table, how I can index both tables to search the parent row, and all child rows in one go for a given term? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-29 : 20:46:53
|
| Create a view that queries the two tables, then create a regular index on that view and finally, create the full-text index on the indexed view. |
 |
|
|
staplebottom
Starting Member
29 Posts |
Posted - 2007-11-30 : 09:29:21
|
| I dont suppose you have an example of this you can show me?Ive create some functions that basically adds the strings from the child table into a single string, so the view has something like this :Select textField, dbo.aggregateTags(@itemID) AS tags FROM tableIf the tags table is changed this wont update the view will it? This is a problem for me if thats the case. |
|
|
|
staplebottom
Starting Member
29 Posts |
Posted - 2007-11-30 : 09:47:40
|
| Create a view that queries the two tables, then create a regular index on that view and finally, create the full-text index on the indexed view.In this case if my full text search matches a string in the baser table, will I not get a match for every field in the child table, as creating a view on a join of the two will create an entry in the view for the base table every time there is a child row?E.G. :tbl_baseid | Text ------------------------1 | BLah blah 2 | Dum de dum3 | HAr HArtbl_base_tagstagID | base_id | TagText --------------------------1 | 1 | first2 | 1 | second3 | 1 | third4 | 2 | fourthThen the view that queries these two tables will havevw_tablesbase_id | tagid | Text | TagText--------------------------------------------- 1 | 1 | Blah blah | first 1 | 2 | blah blah | second 1 | 3 | Blah blah | third 2 | 4 | Dum de dum | fourthand so on with replication of the text field from the base table instead of :base_id | Text | Tags------------------------------------- 1 | Blah blah | first second third 2 | dum de dum | fourthI hope this is clear.Thanks,C |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-30 : 20:35:00
|
| You don't need to aggregate the child rows, it sounds like you're looking for the parent row ID of a parent that either contains the search text or that has a child that contains the search text.So the view will return multiple rows per parent, but that's fine, just put the full-text predicate in the WHERE clause to find the parent row IDs and then use them to get the individual rows however you want them. |
|
|
|
|
|
|
|
|
Full Text Indexing Question