2

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)
  • With this schema you will simply have to perform a UNION ALL between multiple tables (or UNION or otherwise eliminate duplicates if they are even possible). – Aaron Bertrand Dec 23 '15 at 17:08
  • @AaronBertrand If I union the tables, won't I lose the relationships? Not sure what you mean. Thx – Avrohom Yisroel Dec 23 '15 at 17:30
  • Your schema looks kind of a mess to me to be honest, so not sure what you mean by losing the relationships (you yourself said you don't know how "creating joins" would help, but I don't know what you mean by that, either). If you want FTS to work against these different tables, you'll need to run FT functions independently against each of them. – Aaron Bertrand Dec 23 '15 at 17:35
  • @AaronBertrand Not sure why the schema looks a mess. We have tickets, each of which can have notes. A ticket can optionally be linked to one of a standard set of problems and solutions. The only messy bit I can see is that the note text is stored in the Actions table, not with the note itself. That was someone's bad design many years ago, and we're stuck with it. What other problems can you see with it? – Avrohom Yisroel Dec 23 '15 at 17:45
  • @AaronBertrand What I meant about losing the relationships was that if I use union, won't I end up with a set of results that has one entry for each ticket, a separate entry for each ProblemAndSolution and one entry for each note (assuming we combined the actions into the notes)? Each of these would be ranked according to how the keywords appeared in that entry, and I wouldn't know how to rank the tickets overall, bearing in mind I want to take all fields into account when ranking. Does that make it any clearer? If I've misunderstood, pls could you show me some SQL to explain what you meant. – Avrohom Yisroel Dec 23 '15 at 17:49
1

You have two options available:

  1. Create separate Full-Text Indexes on each table and UNION the separate query results. You would have to use your own meta-ranking criteria as rankings across different Full-Text indexes are not comparable. This could result in fairly complicated and nested queries to achieve the desired results.

  2. Create another table that contains all the searchable fields for your tickets and create a single Full-Text Index on that table. You can either consolidate all the text to one column or have a distinct column per table, depends on your search options and how you want to do your rankings. This allows you to use the inherent ranking capabilities of FreeTextTable across all the relevant data. This option requires more effort to maintain the searchable text for each ticket but will be much easier to query, and depending on your ranking criteria possibly more consistent and reliable rankings.

If you are looking for specific keywords you should look into using CONTAINSTABLE instead of FREETEXTTABLE, the latter is intended for matching meaning versus exact words for the former.

| improve this answer | |
  • As it happens, option 2 is (pretty much) exactly what I ended up doing. I forgot to come back here and update the post though, so I'll mark your reply as the answer. If you want to see the agonies I went through to get it all to work, please read my blog post on the subject... dotnetwhatnot.pixata.co.uk/2016/02/16/… – Avrohom Yisroel Feb 21 '16 at 16:23
  • In case anyone wants to read my blog post (which is a bit long, but quite funny!), the link above is now dead, and has been replaced with pixata.co.uk/2015/12/31/… – Avrohom Yisroel Oct 23 '19 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.