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.

Assumptions:

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.

USE <VaultName>

;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=V.RevisionNo 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.