I've been working on a SQL Query that will function as a robust keyword search. I was hoping to get some help in looking at this Query to simplify it. I think it's too complicated, but is the closest to achieving my desired results. Currently seems to return same results as EPDM Search, I think lol.
1) I don't know what field to search on, a search card is linked to individual variables. I want a google style box. I know there is something similar in EPDM.
2) I want my search criteria to span all variables, all configurations, return the Variables where the search criteria was found in.
3) return a filename/documentID only if that search criteria is used in the latest version of that file.
4) Tables Variablevalue,Documents,Variable.
This is more of a time filler exercise, final output will probably be a Google style company web-app. I drove myself crazy with Joins, but finally went with storing a DataSet 1st then searching that DS as it held only my master search results.
;WITH DS AS (SELECT DocumentID,VariableID,ValueText,RevisionNo FROM VariableValue WHERE ValueText LIKE '%<SearchME>%')
SELECT DS.*,V.RevisionNo,D.FileName,R.VariableName FROM DS,VariableValue V,Documents D,Variable R
WHERE V.DocumentID=DS.DocumentID AND V.VariableID=DS.VariableID AND
V.DocumentID=d.DocumentID AND V.VariableID=R.VariableID AND
DS.RevisionNo=(SELECT MAX(Vr.RevisionNO) FROM VariableValue Vr WHERE Vr.DocumentID=DS.DocumentID AND Vr.VariableID=DS.VariableID)
ORDER BY V.VariableID,V.DocumentID
My DB is not huge, so it's hard for me to see how this query will perform. If anyone has a query that does this and wants to share, please do. I tend to over complicate things.