CODEWITHSUNDEEP
sqlsql-server
Alessandro
Alessandro

Reputation: 385

sql IN operator

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?

Upvotes: 1

Views: 209

Answers (3)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55479

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

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452947

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)

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166326

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

Upvotes: 0

Related Questions