I've a table name keywordsTable from where I need to select keywords and need to find the count, how many times they were mentioned in another table named dataTable.
Here's some dummy data of keywordsTable:
id userId context keywords
-----------------------------------------
1 admin Hair Products Shampoo
2 admin Hair Products Hair Conditionar
3 admin Agriculture Wheat
4 admin Hair Products Minoxidil
And here's some dummy data of dataTable
id data
------------
1 No matter the event, Sunsilk Black Shine Shampoo with Amla Pearls helps your hair shine upto 24 hrs. So even in heat and sweat it gives your hair upto 24 hrs shine!
2 Choose Head & Shoulders and dandruff will never be a problem again
3 Amazing hair recovery with minoxidil
4 Expansion of agriculture is reducing forest areas
Here's what I've tried so far.
drop table if exists #searchKeywords
select keywords
into #searchKeywords
from keywordTable
where context = 'Hair Products' and userId= 'admin'
select *
from #searchKeywords
select count(*)
from dataTable
where data like ((Select '%'+keywords+'%' from #searchKeywords))
But I'm getting this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The like operator doesn't work in the way you are trying to use it. Instead try a join e.g.
select count(*)
from dataTable T
inner join #searchKeywords K on T.[data] like '%' + K.keywords + '%'