D
David Wetmore
I want to write a minimal search routine with a user-determined number
of search terms. In other words, given a set of search terms A through Z, I
want to do an (A AND B) AND C) AND D).... search where the number and order of
the search terms is determined by the user.
Instead of trying to nest a series of inner joins, I am trying the following
strategy: Put set A in a temporary table tblHits, containg two flags, new, and newer.
Flag all the A values as new. Do an inner join with B, which is in tblCandidates
Then throw away those values in A which do not fit the search and use the reduced
set as B joined with C and so forth. At the heart of this lies:
UPDATE tblHits SET tblHits.Newer = 1
WHERE (SELECT tblHits.HKey
FROM tblHits INNER JOIN tblCandidates ON tblHits.HKey = tblCandidates.HKey)
Both halves of the SQL statement (the update and the select) work alone, but not
when the Select is in a WHEN clause.
Am I on the right track, or is there a "standard" solution to this problem?
of search terms. In other words, given a set of search terms A through Z, I
want to do an (A AND B) AND C) AND D).... search where the number and order of
the search terms is determined by the user.
Instead of trying to nest a series of inner joins, I am trying the following
strategy: Put set A in a temporary table tblHits, containg two flags, new, and newer.
Flag all the A values as new. Do an inner join with B, which is in tblCandidates
Then throw away those values in A which do not fit the search and use the reduced
set as B joined with C and so forth. At the heart of this lies:
UPDATE tblHits SET tblHits.Newer = 1
WHERE (SELECT tblHits.HKey
FROM tblHits INNER JOIN tblCandidates ON tblHits.HKey = tblCandidates.HKey)
Both halves of the SQL statement (the update and the select) work alone, but not
when the Select is in a WHEN clause.
Am I on the right track, or is there a "standard" solution to this problem?