?
Solved

Search Query in SQl server 2012

Posted on 2014-09-01
5
Medium Priority
?
150 Views
Last Modified: 2014-09-02
I have two tables Articles and Article_Keywords
Here is the table structure
Articles
1. ArticleID (PK)
2. Title
3. Details
Article_Keywords
1. ArticleKeywordID (PK)
2. ArticleID
3. Keyword


Articles are to stored users article which has a title and a details field.
Article_Keywords is used to keywords for an article, it can be one or many for a single article.

Now, I need to give search feature to the users where user can search on article Keywords. Also there is a advance search option where they can search on keywords , title and details.


I am not good in writing sql queries
but what I was thinking of writing query for the first option (search only in keywords)
is select * from articles where articleid in (select articleid from Article_Keywords where keywords like %@parameters%)

And for the second part (search in keywords, title and details)
select * from articles where articleid in (select articleid from Article_Keywords where keywords like %@parameters%)
and title like %@parameters% and details like %@parameters%


I need know will these would be the best option to do search in these tables
0
Comment
Question by:yadavdep
  • 2
  • 2
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40296525
your query is almost fine, just a small detail:
select * from articles where articleid in (select articleid from Article_Keywords where keywords like '%' + @parameters + '%')

this article is what you need to read up also;
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40296614
I wouldn't use a subselect since a inner join will do the job:
select articles.Title, articles.Details
from articles 
inner join Article_Keywords 
  on Article_Keywords.ArticleID  = articles.ArticleID 
where Article_Keywords.keywords like '%SearchTextHere%'


select articles.Title, articles.Details
from articles 
inner join Article_Keywords 
  on Article_Keywords.ArticleID  = articles.ArticleID 
where Article_Keywords.keywords like '%SearchTextHere%'
 or title like '%SearchTextHere%' 
 or details like '%SearchTextHere%'

Open in new window


Note that I changed the AND to OR because I'm assuming that you want to find a given text in keywords, title or details. With AND will work only if these 3 fields have the text you are searching for.
0
 

Author Comment

by:yadavdep
ID: 40296617
Hi Vitor,

would joins be better in terms of performance ?
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40296630
If ArticleID in Article_Keywords table it's a foreign key (FK) I would advice you to use Inner Join so the engine can use index.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40296671
IN and JOIN as well as EXISTS can and may use indexes....
in regards to the 3rd syntax:
select * 
from articles a
where exists (select null from Article_Keywords ak where ak.keywords like %@parameters%  and ak.articleid = a.articleid  )

Open in new window

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 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