Doing a CONTAINS search on multiple tables and columns combined.

I want to do a CONTAINS search on several columns combined.
This example searches each column separately, so that a record is not
included that has one of the search terms in the Desired Position and the
other two search terms in a section body.
SET @.SearchPhrase = 'Engineer AND SQL AND VB'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
FROM
dbo.tblResumes LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID
WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
How could I return a resume that has all three terms, but not in any one
column?
Also, I don't understand how the search works when their are several
tblResumeSections records for one tblResumes record. Does it search each
section record?
I am grateful for any suggestions.
As I've played with this in light of other recent similar posts, the only
approach I can see at this point is to create the query in code and send it
to the database server. This allows me to dynamically build the query to
allow for a variable number of search terms.
This must be a very common search and newsgroup queston: how to do an 'AND'
search invoving multiple tables and columns where all of a variable number
of search terms must be found in a row, but not any one column..
I can't find a way to do this with FULL TEXT searches in a stored procedure
so far. I get an error if I try to concatenate columns in a CONTAINS search
as below.
Again, any suggestions are very welcome.
The following query is constrcted in code on the web page and does a LIKE
search on a concatenation of the relevant columns. It works but falls short
in that a resume that has the search terms in different sections (child
table) is not returned. But it seems the best I can do for now.
DECLARE @.SearchPhrase VarChar(50)
, @.Role VarChar(15)
SET @.SearchPhrase = 'SQL AND VB AND Engineer'
SET @.Role = 'Job Seeker'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
, dbo.tblPostionTypes.Abbr AS PType
, dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS
JobSeekerName
, dbo.tblResumes.DateRevised AS Posted
, dbo.tblContacts.City + ', ' + dbo.tblContacts.State AS CityState
FROM
dbo.tblResumes INNER JOIN
dbo.tblContacts ON dbo.tblResumes.ContactID = dbo.tblContacts.ContactID
INNER JOIN
dbo.tblPostionTypes ON dbo.tblResumes.PositionTypeID =
dbo.tblPostionTypes.PositionTypeID LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID LEFT OUTER JOIN
dbo.tblExperiences ON dbo.tblResumeSections.ResSectionID =
dbo.tblExperiences.ResSectionID LEFT OUTER JOIN
dbo.tblQualifications ON dbo.tblResumes.ResumeID =
dbo.tblQualifications.ResumeID
WHERE dbo.tblResumes.DateStart < GETDATE()
AND dbo.tblResumes.DateStop > GETDATE()
AND dbo.tblResumes.Deleted Is Null
AND dbo.tblResumes.Active = 1
AND dbo.tblResumes.Approved = 1
AND dbo.tblContacts.Active = 1
AND dbo.tblContacts.Approved = 1
AND dbo.tblContacts.Role = @.Role
-- Keyword tests
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%SQL%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%VB%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%Engineer%'
"GM" <gmdevREMOVE@.starband.net> wrote in message
news:OiI%23kzSREHA.3140@.tk2msftngp13.phx.gbl...
> I want to do a CONTAINS search on several columns combined.
> This example searches each column separately, so that a record is not
> included that has one of the search terms in the Desired Position and the
> other two search terms in a section body.
> SET @.SearchPhrase = 'Engineer AND SQL AND VB'
> SELECT DISTINCT
> dbo.tblResumes.ResumeID
> , dbo.tblResumes.DesiredPosition
> FROM
> dbo.tblResumes LEFT OUTER JOIN
> dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
> dbo.tblResumeSections.ResumeID
> WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
> How could I return a resume that has all three terms, but not in any one
> column?
>
> Also, I don't understand how the search works when their are several
> tblResumeSections records for one tblResumes record. Does it search each
> section record?
> I am grateful for any suggestions.
>