Search for multiple words in multiple columns

[dragonwell]

Hi,

warning -the logic here may be flawed (I'm tired...)

I'm trying to create a sp to search a catalog database. The view I'm querying has columns like item-name, size, color, description, etc.

I want the user to be able to submit a search string like 'small red shoe' and return only records that "match". If the search string is 'small shoe', obviously return all those records plus ones of any color.

Assuming I split the searchstring into a table variable, I have something like

decare @words table(word varchar(100))
insert into @words select '%small%'
insert into @words select '%red%'
insert into @words select '%shoe%'


select * from productview p
join @words w on 
(p.itemname like w.word
or p.size like w.word
or p.color like w.word
or p.description like w.word)

the problem is, it returns all the "shoes" regardless of color.

Any ideas on how to get this working are welcome.

 

[Otto]

select * from productview p
join @words w on
(p.itemname is null or p.itemname like w.word) and
(p.size is null or p.size like w.word) and
(p.color is null or p.color like w.word) and
(p.description is null or p.description like w.word)