Solved

SQL Server issue with like operator

Posted on 2014-11-26
15
105 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 55

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 55

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 55

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

688 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