tsql - Search selected tabels in database and line up results?

Question

I'm trying to build a sarch page on my case management system, but I'm struggling to get something dynamic in place. I would like to search multiple tables for a given string. And return a list of cases these refer to.

Ie I have 3 tables (the project include several more, but to explain I just use 3).

1: Case main table, including caseID, title, and description.

2: notes table, including a ntext note field. This is 1:* from the case table so each case can have multiple notes

3: adress table, including street and city for the case This is also 1:* from the case table so each case can have multiple addresses

I would like to search for ie "Sunset Boulevard", and if the string is found in either the case title, the note or the address I would like to return the list of cases that match.

I can do a normal SELECT statement at get the caseID and Title and in the WHERE Clause specify which to include, ie:

SELECT CaseID, Title 
FROM Cases
WHERE Cases.caseID IN ( SELECT CaseID FROM notes WHERE Notes.note like '%Sunset boulevard%' )
   OR Cases.caseID IN ( SELECT CaseID FROM address WHERE address.address1 like '%Sunset boulevard%' )

And then expand the where clause to all columns I want to search. But that won't give me any hint on where the searched string has been found.

I also found another article here https://stackoverflow.com/a/709137 and could use this to search entire database for fields, but this will still not give me a list of cases.

Anyone got a "best practice" for creating small search engine on website?

question from:https://stackoverflow.com/questions/65642777/search-selected-tabels-in-database-and-line-up-results

Answer 1

Best practice will be to move so massive search functionality outside OLTP area and use search engine: eg. Solr, Sphinx, Elasticsearch etc.