sql IN operator

Question

Hi. I have the sql below but can not get it to work

select product_category, 
       (
        select top 1 sub_category 
          from sub_categories 
         where product_category IN (keywords)
       ) as s 
  from products;

product category is Baby Gift Baskets and the keywords field is Baby Gift Baskets,Baby Gifts

basically want to get sub_category when product_category is found in the keywords list?

Answer 1

This will not work for you, as the keywords is seen as a single item.

You will have to split the values from keyword to be able to use an IN

Have a look at

Split Function

or

Split XML style

Answer 2

You would need to use

where ',' + keywords + ',' like '%,'+ product_category + ',%'

this would be much easier and more efficient with a normalised database structure. (Your current structure with multiple keywords in a single column violates first normal form)

Answer 3

This will not work. You can change this to use LIKE -

select top 1 sub_category 
  from sub_categories 
 where keywords like '%' + product_category + '%';