SQL Server issue with like operator

I have two tables
Articles - ArticleID, Details
Artilces_Keywords - ArticleKeywordID, ArticleID, Keyword

Now from the application user can search an articles based on Article.Detail , Artilces_Keywords .Keyword or both fields

I used this query to get the results when user does a search on Artilces_Keywordds .Keyword

select distinct a.* from Articles a
inner join Article_Keywords ak on ak.ArticleID = a.ArticleID
where ak.Keyword LIKE 'snow%' or ak.Keyword LIKE 'plow%'

This query gives records who has keyword like snow or plow.

Now, my client says to change it and he want only those records which has all the keywords which he entered in the search box.
Means he wants only articles which has keyword 'snow' and 'plow' insead of or

I changed the query

select distinct a.* from Articles a
inner join Article_Keywords ak on ak.ArticleID = a.ArticleID
where ak.Keyword LIKE 'snow%' and ak.Keyword LIKE 'plow%'

but this does not returned me any record.


I need help in this query where I can find records which has all the keywords which user enters
yadavdepAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dung DinhDBA and Business Intelligence DeveloperCommented:
You should change as below
SELECT DISTINCT a.* FROM Articles a
  INNER JOIN Artilces_Keywords ak ON ak.ArticleID = a.ArticleID
WHERE ak.Keyword LIKE '%snow%plow%'

Open in new window


With this condition, the query will return any article  that keyword contains both snow and plow.

Note: in case your data is large, maybe you will encounter performance issue because SQL engine will scan full your table.
0
chaauCommented:
You need to adjust the query. You have several ways of accomplishing this. One of the easiest one is to join to Article_Keywords table twice:
select distinct a.* from Articles a
inner join Article_Keywords ak1 on ak1.ArticleID = a.ArticleID AND ak1.Keyword LIKE 'snow%'
inner join Article_Keywords a2 on ak2.ArticleID = a.ArticleID and ak2.Keyword LIKE 'plow%'

Open in new window

Alternatively you can use this query:
select distinct a.* from Articles a
inner join (
SELECT ArticleID FROM Article_Keywords 
where Keyword LIKE 'snow%' and Keyword LIKE 'plow%'
GROUP BY ArticleID
HAVING COUNT(DISTINCT Keyword) >= 2) ak on ak.ArticleID = a.ArticleID

Open in new window

0
yadavdepAuthor Commented:
Dung,

I tried this but because under keywords like 'snow', 'plow' are stored as separate rows, this query does not returned me anything

Here is sample data of Article_Keywords table
ArticleKeywordID  [ArticleID] [Keyword]
1                                1                snow
2                                1                 plow
3                                1                  rest
4                                5                  west
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

yadavdepAuthor Commented:
chaau,

Sorry but none of the query works for me.
In the question I took example of two words 'snow' and 'plow' but it under users hand that on how many keywords he enter in the search box
0
chaauCommented:
Why is the second query not working for you? All you need to do is to count how many entries the user added
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
With your example, I could understand more detail your question. I've just update my script
SELECT a.*,tmp.Keyword
FROM Articles a
INNER JOIN (SELECT 
            ArticleID,
                   STUFF((
					   SELECT ', ' + KeyWord
					   FROM Artilces_Keywords ak 
					   WHERE (ak.ArticleID = a.ArticleID) 
					   FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
					   ,1,2,'') AS Keyword
			FROM Articles a
			GROUP BY ArticleID) tmp ON a.ArticleID = tmp.ArticleID
WHERE tmp.Keyword LIKE '%snow%plow%'

Open in new window


In sub query table, I will combine all keywords by ArticleID and then I will search what keyword matches the criteria as your expectation. Depend on what string your end-users entered, maybe we need to modify it as a parameter before you pass it to your query.

Here is result:
ArticleID   Details     Keyword

1                ABC          snow, plow, rest
0
HainKurtSr. System AnalystCommented:
if not answered yet, maybe this (works on just 2 keywords):

select distinct a.* from Articles a
inner join 
(
select * from Article_Keywords k1 inner join Article_Keywords k2 on k1.ArticleID=k2.ArticleID and k1.keyword<>k2.keyword
where k1.Keyword = 'snow%' and k2.Keyword = 'plow%'
) ak on ak.ArticleID = a.ArticleID

Open in new window

0
yadavdepAuthor Commented:
chaau

You second did not give any complier error neither it returned any records
0
chaauCommented:
Try this

with UserInput as(
select 'snow%' word
union select 'plow%')
select distinct a.* from Articles a
inner join (
SELECT k.ArticleID FROM Article_Keywords k
INNER JOIN UserInput ui ON
where k.Keyword LIKE ui.word
GROUP BY k.ArticleID
HAVING COUNT(DISTINCT k.Keyword) = (SELECT COUNT(*) FROM UserInput)) ak on ak.ArticleID = a.ArticleID
0
HainKurtSr. System AnalystCommented:
i was trying to edit my post above :)

select distinct a.* from Articles a
inner join 
(
 select k1.ArticleID
   from Article_Keywords k1 
  inner join Article_Keywords k2 on k1.ArticleID=k2.ArticleID and k1.keyword<>k2.keyword
  where k1.Keyword like 'snow%' and k2.Keyword like 'plow%'
) ak on ak.ArticleID = a.ArticleID

Open in new window


do you always have 2 keywords or it can be any number, 10, 20?
0
chaauCommented:
Just to correct myself. My second query needed OR, not AND:
select distinct a.* from Articles a
inner join (
SELECT ArticleID FROM Article_Keywords 
where Keyword LIKE 'snow%' or Keyword LIKE 'plow%'
GROUP BY ArticleID
HAVING COUNT(DISTINCT Keyword) >= 2) ak on ak.ArticleID = a.ArticleID

Open in new window

0
chaauCommented:
BTW, the query I have provided here will count the user entries itself
0
HainKurtSr. System AnalystCommented:
no need for "and k1.keyword<>k2.keyword", simplified version here:

select distinct a.* from Articles a
inner join 
(
 select k1.ArticleID
   from Article_Keywords k1 
  inner join Article_Keywords k2 on k1.ArticleID=k2.ArticleID
  where k1.Keyword like 'snow%' and k2.Keyword like 'plow%'
) ak on ak.ArticleID = a.ArticleID

Open in new window

0
chaauCommented:
@Dung: How will your query work for the like '%plow%snow%'?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yadavdepAuthor Commented:
Thank you so much every one.
Now I have many solutions for one issue.

I will close this question in a minute
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.