0

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?

Klaus
  • 1
  • 1
  • [Get the column names matching a multiple column search](https://stackoverflow.com/q/40461595/92546) looks related. Not dynamic, but it could learn. Aside: You correctly state that you are searching for a _string_, not a _word_, i.e. the pattern `'%veal%'` will match `'The wisdom of the ages revealed!'`. – HABO Jan 09 '21 at 22:57
  • Sure, I will try this. Since I'm searching 10 different tables and several columns in each it will be a nightmare of a SQL statement, but seems this is the best way to have full control of the results. – Klaus Jan 10 '21 at 15:54

1 Answers1

0

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

revoua
  • 2,044
  • 1
  • 21
  • 28
  • I was thinking about that, but this is an internal system and all data is in the local database and as susch not available from the outside, and hence adding the external search engine wouldn't allow it to traverse the data. – Klaus Jan 10 '21 at 15:52