Search Query in SQl server 2012

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
yadavdepAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
yadavdepAuthor Commented:
Hi Vitor,

would joins be better in terms of performance ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.