Temp table help 1

[JohnnyLong]

Hi,

I need some advice on the best way to solve the following:

SELECT CandidateID
FROM Notes
WHERE CONTAINS(subject, '"fish*"')
UNION
SELECT CandidateID
FROM Notes
WHERE CONTAINS(comments, '"fish*"')
UNION
SELECT CandidateID
FROM CandidatesNotes
WHERE CONTAINS(CandidateNotes, '"fish*"')

This gives me a result set of CandidateID's. I then need to Select the full candidate record from the Candidates table using the IDs. What's the best way of doing this? Is a temporary table the easist way?

Thanks for looking,

John

 

[bborissov]

SELECT Candidates.*
       FROM Candidates
INNER JOIN Notes ON Candidates.CandidateID = Notes.CandidateID
WHERE CONTAINS(Notes.subject       , '"fish*"') OR
      CONTAINS(Notes.comments      , '"fish*"') OR
      CONTAINS(Notes.CandidateNotes, '"fish*"')

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[JohnnyLong]

Thanks Borislav, but using joins with CONTAINS slows the search up too much. The Union query takes less than 2 seconds but the query with Joins takes 9 seconds.

 

[bborissov]

Hmmm,

SELECT Candidates.*
       FROM Candidates
INNER JOIN (SELECT CandidateID
                   FROM Notes
                   WHERE CONTAINS(subject, '"fish*"')
            UNION
            SELECT CandidateID
                   FROM Notes
                   WHERE CONTAINS(comments, '"fish*"')
            UNION
            SELECT CandidateID
                   FROM CandidatesNotes
                   WHERE CONTAINS(CandidateNotes, '"fish*"'))
Test ON Candidates.CandidateID = Test.CandidateID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[JohnnyLong]

Thanks alot Borislav, that's a 5 second search which is fine. You are a star.

John