I need to do a Full Text Search where the search is applied across multiple columns where the text from all those columns are treated as a single (very long) varchar to filter against and find matches. Full Text Search appears to handle that.

But I also have child elements (1:many) from another table where one of the columns in those related rows need to be included in the search. And parent elements (many:1) where again a column in that related row needs to be included.

Is there a way to do this with Full Text Search?

And in the alternative, I thought of writing a very complex join that does return all the associated data together to search against. But... correct me if I'm wrong, that would be no more efficient than just reading it all into memory to search. Because it has to combine everything before performing the filter and therefore would need to read&combine every row of data.

And if there's another approach that would work, very happy to hear any suggestions. Because at present I think my best bet is to create a table that has a FK to the matching row and a varchar column that has all the text from all the table.columns concatenated and I Full Text Search that. This would be expensive because on every upsert of any matching row of data, I have to update this table.