SQL Server Full-Text Search – Using freetexttable Against Multiple Tables

I have a database which (amongst other things) contains tables for support tickets. Each ticket has a short summary, as well as a description. Each ticket can have multiple notes, each of which has an associated action which contains details. As well as this, a ticket can optionally have a ProblemAndSolution, which is a standard problem and solution.

The (cut down) schema for this looks like this…

create table ProblemAndSolutions (
ID int not null identity(1,1) primary key,
Problem varchar(max) not null default '',
Solution varchar(max) not null default '',
)
go

create table Actions (
ID int not null identity(1,1) primary key,
Details varchar(max) not null default '',
)
go

create table SupportTickets (
ID int not null identity(1,1) primary key,
ShortSummary varchar(max) not null default '',
Description varchar(max) not null default '',
ProblemAndSolutionID int foreign key references ProblemAndSolutions(ID),
)
go

create table SupportTicketNotes (
ID int not null identity(1,1) primary key,
SupportTicketID int not null foreign key references SupportTickets(ID),
ActionID int not null foreign key references Actions(ID),
)
go

I have been asked to provide a feature where users can enter keywords, and will be shown relevant tickets, where all the varchar fields in the tables above are to be searched. In other words, they want to see tickets where the keywords appear in the ticket short summary or description, and/or in the problem and/or solution, and/or in the note's Action.Details field. Obviously, the more times it appears, the higher the rank.

This seemed to be an ideal job for freetexttable, and indeed on one table, this works great. However, I'm struggling to see how to do the search across all of the tables. I've seen a lot of answers about creating joins, but I don't see how this would help, as you would end up with multiple results for each ticket, if the keywords appeared in multiple ProblemAndSolutions and note/actions.

What I want is to end up with a set of results where each ticket only appears once, and its rank is based on the keywords being found in any of the varchar fields shown above.

Anyone able to explain how I do this?

Edit In order to make it clearer, here is some (made up on the fly and therefore dumb) sample data…

Ticket #1

ShortSummary = "computer broken"

Description = "the computer is broken"

Linked to P&S #1 (see below)

Has a note whose Action.Details contains the word "computer" once

Ticket #2

ShortSummary = "broken system"

Description = "the computer system is broken"

Not linked to a P&S

Has a note whose Action.Details contains the word "computer" twice

P&S #1

Problem = "The computer does not work"

Solution = "The computer is broken and needs replacing"

Now, ticket #1 uses the word "computer" five times, once in the summary and description, once in a note and twice in the linked P&S. Ticket #2 uses the word "computer" three times, once in the description, and twice in the note.

I would expect a search on the word "computer" to rank ticket #1 above #2 as the keyword appears more times.

Edit #2

In order to make it easier to try this out, here is some SQL to generate sample data. This isn't exactly the same as the samples I showed above, but the principle is the same…

insert ProblemAndSolutions (Problem, Solution) values ('computer not working', 'computer broken')
insert ProblemAndSolutions (Problem, Solution) values ('unclear error report', 'stupid customer, tell them to buy a new computer')
insert ProblemAndSolutions (Problem, Solution) values ('crashed computer', 'the computer is a broken computer')

insert Actions (Details) values ('we checked it and id did not work when we tried to start it')
insert Actions (Details) values ('the computer is completely broken and needs replacing')
insert Actions (Details) values ('we called, but the customer was out. will call back again')
insert Actions (Details) values ('brand new computer, so probably not a fault. more likely a stupid customer')
insert Actions (Details) values ('customer reported something going wrong, but we couldn''t find anything')

insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer broken', 'the computer is completely broken and is not working', 1)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('broken computer', 'we have broken the pc today', NULL)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer crash', 'the machine crashed when we tried to start it', 2)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer crash computer', 'the compter crashed when we tried to start the computer', 3)

insert SupportTicketNotes (SupportTicketID, ActionID) values (1, 1)
insert SupportTicketNotes (SupportTicketID, ActionID) values (1, 2)
insert SupportTicketNotes (SupportTicketID, ActionID) values (2, 3)
insert SupportTicketNotes (SupportTicketID, ActionID) values (2, 4)
insert SupportTicketNotes (SupportTicketID, ActionID) values (3, 5)