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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…