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%'




[quoted text, click to view]