Complex search Functionality with joins

Question

Post reply

Complex search Functionality with joins

born2achieve

Hall of Fame

Points: 3182
More actions
February 19, 2014 at 3:37 pm

#289650

Hi,
Here is the sample table with data
with Cooking as
(
select 1000 as IDCooking, 'Cooking Waffles' as VariertyName,'2013-08-23' as createddt union all
select 1001 as IDCooking, 'Cooking Candy' as VariertyNameName,'2013-08-22' as createddt union all
select 1002 as IDCooking, 'Cooking Chips' as VariertyNameName,'2013-05-11' as createddt union all
select 1003 as IDCooking, 'Cooking Pizza' as VariertyNameName,'2014-01-18' as createddt union all
)
with Cooking_Details as
(
select 1 as IdDetail,1000 as IDCooking, 'Dominos' as Provider union all
select 2 as IdDetail,1001 as IDCooking, 'Nestle' as Provider union all
select 3 as IdDetail,1002 as IDCooking, 'Lays' as Provider union all
select 4 as IdDetail,1003 as IDCooking, 'PizzaHut' as Provider union all
)
with Cooking_Contents as
(
select 1 as idContent,1000 as IDCooking, 'Sugar' as Item union all
select 2 as idContent,1000 as IDCooking, 'Salt' as Item union all
select 3 as idContent,1000 as IDCooking, 'Chilly' as Item union all
select 4 as idContent,1000 as IDCooking, 'Wheat' as Item union all
select 5 as idContent,1001 as IDCooking, 'Sugar cane' as Item union all
select 6 as idContent,1001 as IDCooking, 'Olive' as Item union all
select 7 as idContent,1001 as IDCooking, 'Milk' as Item union all
select 8 as idContent,1003 as IDCooking, 'potato' as Item union all
select 9 as idContent,1003 as IDCooking, 'Salt' as Item union all
select 10 as idContent,1003 as IDCooking, 'sesame oil' as Item union all
select 11 as idContent,1003 as IDCooking, 'mustard' as Item union all
)
My output columns are
IdCooking, VariertyName,provider,createddt
i am trying to create search functionality and my input paramentes are either item/VariertyName
for example if i pass "sugar" as search parameter then my required output should be
1000, Cooking Waffles,Dominos,2013-08-23
1001, Cooking Candy,Nestle,2013-08-22
for example if i pass "Cooking Waffles" as search parameter then my required output should be
1000, Cooking Waffles,Dominos,2013-08-23
The motive here is i need to search the column VariertyName on cooking table or item on
Cooking_Contents table.
If use left join b/w tables it gives bad result to me. How can join these tables to get proper results when do search.
Any sample query please

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Not sure what you mean that left join gives bad result.

This is not ideal because of the wildcard search but it does work.

declare @SearchVal varchar(50) = 'sugar';

with Cooking as

(

select 1000 as IDCooking, 'Cooking Waffles' as VariertyName,'2013-08-23' as createddt union all

select 1001 as IDCooking, 'Cooking Candy' as VariertyNameName,'2013-08-22' as createddt union all

select 1002 as IDCooking, 'Cooking Chips' as VariertyNameName,'2013-05-11' as createddt union all

select 1003 as IDCooking, 'Cooking Pizza' as VariertyNameName,'2014-01-18' as createddt

)

,

Cooking_Details as

(

select 1 as IdDetail,1000 as IDCooking, 'Dominos' as Provider union all

select 2 as IdDetail,1001 as IDCooking, 'Nestle' as Provider union all

select 3 as IdDetail,1002 as IDCooking, 'Lays' as Provider union all

select 4 as IdDetail,1003 as IDCooking, 'PizzaHut' as Provider

)

,

Cooking_Contents as

(

select 1 as idContent,1000 as IDCooking, 'Sugar' as Item union all

select 2 as idContent,1000 as IDCooking, 'Salt' as Item union all

select 3 as idContent,1000 as IDCooking, 'Chilly' as Item union all

select 4 as idContent,1000 as IDCooking, 'Wheat' as Item union all

select 5 as idContent,1001 as IDCooking, 'Sugar cane' as Item union all

select 6 as idContent,1001 as IDCooking, 'Olive' as Item union all

select 7 as idContent,1001 as IDCooking, 'Milk' as Item union all

select 8 as idContent,1003 as IDCooking, 'potato' as Item union all

select 9 as idContent,1003 as IDCooking, 'Salt' as Item union all

select 10 as idContent,1003 as IDCooking, 'sesame oil' as Item union all

select 11 as idContent,1003 as IDCooking, 'mustard' as Item

)

select c.IDCooking, c.VariertyName, c.createddt

from Cooking c

left join Cooking_Details cd on cd.IDCooking = c.IDCooking

left join Cooking_Contents cc on cc.IDCooking = c.IDCooking

where c.VariertyName like '%' + @SearchVal + '%'

or cd.Provider like '%' + @SearchVal + '%'

or cc.Item like '%' + @SearchVal + '%'

group by c.IDCooking, c.VariertyName, c.createddt

You might also take a look at Gail's article about catch-all queries (although as I write this her blog is down).

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I'm not sure that your expected results are correct because you say that if you pass "Cooking Candy" as a parameter, you should get "Cooking Waffles". If that's incorrect, this might work for you.

SELECT c.IDCooking, c.VariertyName, d.Provider, c.createddt

FROM Cooking c

JOIN Cooking_Details d ON c.IDCooking = d.IDCooking

WHERE c.IDCooking IN ( SELECT IDCooking

FROM Cooking

WHERE VariertyName LIKE '%' + @SearchString + '%'

UNION ALL

SELECT IDCooking

FROM Cooking_Contents

WHERE Item LIKE '%' + @SearchString + '%')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 3

Hi Sean and Luis thanks for the reply

Hi Luis,

I am sorry, small mistake i made. i did correct on my first post.

for example if i pass "Cooking Waffles" as search parameter then my required output should be

1000, Cooking Waffles,Dominos,2013-08-23

Could you please help me now

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

Hi Luis,

Here is my try and please suggest me about this.

--DECLARE @val VARCHAR(50) = 'sugar';

DECLARE @val VARCHAR(50) = 'Cooking Waffles';

SELECT DISTINCT

Cooking.*,Cooking_Details.Provider

FROM

Cooking

INNER JOIN

Cooking_Details

ON Cooking.IDCooking = Cooking_Details.IDCooking

INNER JOIN

Cooking_Contents

ON Cooking.IDCooking = Cooking_Contents.IDCooking

WHERE

Cooking_Contents.item LIKE '%' + @val + '%'

OR Cooking.VariertyName LIKE '%' + @val + '%';

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

born2achieve (2/19/2014)
Hi Sean and Luis thanks for the reply
Hi Luis,
I am sorry, small mistake i made. i did correct on my first post.
for example if i pass "Cooking Waffles" as search parameter then my required output should be
1000, Cooking Waffles,Dominos,2013-08-23
Could you please help me now

The query I posted will return exactly that. Can you explain why it is not what you need?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

born2achieve Hall of Fame Points: 3182 More actions · Answer 6

Hi Sean,

I apologize for my mistake that i didn't see your sample code. It's perfect code gives expected result. Thank you so much.Also you posted a Gail's link which is broken link. can i have the exact link for the article please. would like to learn.

Thank you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Sean Lange (2/19/2014)
You might also take a look at Gail's article about catch-all queries (although as I write this her blog is down).
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

Still fighting with hosting provider and domain registrar. Day 4.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

born2achieve (2/20/2014)
can i have the exact link for the article please. would like to learn.

The link given is correct.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

born2achieve Hall of Fame Points: 3182 More actions · Answer 9

Hi Gila,

nope. here is the issue am getting.

Oops! Google Chrome could not find sqlinthewild.co.za

Try reloading: sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

I have 20 mbps internet connection. my internet is working great.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

born2achieve (2/20/2014)
Hi Gila,
nope.

*sigh* Yes.

The link is correct. The site (as I'm well aware and as I explained above) is down in its entirety.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

born2achieve Hall of Fame Points: 3182 More actions · Answer 11

I understand that the site is down. any clue when will be up. i would like to read your article.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

born2achieve (2/20/2014)
I understand that the site is down. any clue when will be up.

GilaMonster (2/20/2014)
Still fighting with hosting provider and domain registrar. Day 4.

Try http://www.google.com/#q=sqlinthewild+catch+all and view the cached version google has saved

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

born2achieve Hall of Fame Points: 3182 More actions · Answer 13

Though i hit the cached link still the same message from browser. Will wait to read your article when it's up.

Attached my screen for your reference

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

born2achieve (2/20/2014)
Though i hit the cached link still the same message from browser.

I just tried it and the google cache works. Click the triangle next to the search result in google and select 'cached'. It takes a while and there's no CSS or images, but the text is there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass