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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
@Dung: How will your query work for the like '%plow%snow%'?
0
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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 DinhConnect With a Mentor DBA 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
 
HainKurtConnect With a Mentor Sr. 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
 
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
All Courses

From novice to tech pro — start learning today.