Solved

SQL Server issue with like operator

Posted on 2014-11-26
15
104 Views
Last Modified: 2014-12-22
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
0
Comment
Question by:yadavdep
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 8

Expert Comment

by:Dung Dinh
ID: 40468327
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
 
LVL 25

Expert Comment

by:chaau
ID: 40468328
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
 

Author Comment

by:yadavdep
ID: 40468329
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:yadavdep
ID: 40468330
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
 
LVL 25

Expert Comment

by:chaau
ID: 40468331
Why is the second query not working for you? All you need to do is to count how many entries the user added
0
 
LVL 8

Assisted Solution

by:Dung Dinh
Dung Dinh earned 167 total points
ID: 40468335
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
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 166 total points
ID: 40468338
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
 

Author Comment

by:yadavdep
ID: 40468340
chaau

You second did not give any complier error neither it returned any records
0
 
LVL 25

Expert Comment

by:chaau
ID: 40468341
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40468342
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
 
LVL 25

Expert Comment

by:chaau
ID: 40468343
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
 
LVL 25

Expert Comment

by:chaau
ID: 40468344
BTW, the query I have provided here will count the user entries itself
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40468347
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
 
LVL 25

Accepted Solution

by:
chaau earned 167 total points
ID: 40468348
@Dung: How will your query work for the like '%plow%snow%'?
0
 

Author Comment

by:yadavdep
ID: 40468352
Thank you so much every one.
Now I have many solutions for one issue.

I will close this question in a minute
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question