?
Solved

SQL Server issue with like operator

Posted on 2014-11-26
15
Medium Priority
?
106 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 668 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 57

Assisted Solution

by:HainKurt
HainKurt earned 664 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 57

Expert Comment

by:HainKurt
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 57

Expert Comment

by:HainKurt
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 668 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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