CODEWITHSUNDEEP
sqlsql-server
Thanigaivelan
Thanigaivelan

Reputation: 175

Search the Keywords present in the title

I have two tables

1.products fields

1.product_id
2.product_title,
3.product_desc
4.product_img

2.keywords fields

1. id
2. keyword

keywords will be 

 1.hair
 2 body
 3 tv
 4 mobile

product_title will be

1. Hair oil
2. Hair Straightner
3. Body oil
4. Body massage
5. LCD TV 32"
6. LED TV 40"
7. Air conditioner
8. Washing machine
9. Refrigrator

According to the keyword present in the title it have to show listing, for here it have to show products from 1 to 6 ,it should not show the 7 to 9. How I can do it?

Upvotes: 0

Views: 75

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82504

IMHO, The best thing to do is add a table productsToKeywords that will allow you many to many relationship between the products and Keywords tables. 

create table productsToKeywords
(
    ptk_product_id int foreigm key reference products (product_id),
    ptk_keyword_id int foreigm key reference keywords (id),
    primary key(ptk_product_id, ptk_keyword_id)
)

Then your select would look something like this:

select product_id, product_title,, product_desc, product_img
from products 
inner join productsToKeywords on(product_id = ptk_product_id)
inner join Keywords on(ptk_keyword_id = id)
where keyword = 'hair'

Upvotes: 1

wonderbell
wonderbell

Reputation: 1126

You can try this:

  select p.* from Product p
    inner join Keyword k
    on p.product_title like '%' + k.keyword + '%'

If it is possible to have empty values in keyword or title fields, then you need to add condition to ignore such records too. Otherwise it would match with all.

Upvotes: 0

shA.t
shA.t

Reputation: 16968

I suggest you to use EXISTS like this:

SELECT *
FROM products p
WHERE EXISTS( SELECT 1
              FROM keywords k
              WHERE p.product_title Like '%' + k.keyword + '%')

Upvotes: 0

Related Questions