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: 286562 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: 183686 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

born2achieve

Hall of Fame

Points: 3182

February 19, 2014 at 4:43 pm

#1690508

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: 286562 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

born2achieve

Hall of Fame

Points: 3182

February 20, 2014 at 5:11 am

#1690616

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: 1004494 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: 1004494 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

born2achieve

Hall of Fame

Points: 3182

February 20, 2014 at 5:39 am

#1690627

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: 1004494 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

born2achieve

Hall of Fame

Points: 3182

February 20, 2014 at 5:45 am

#1690634

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: 1004494 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

born2achieve

Hall of Fame

Points: 3182

February 20, 2014 at 5:52 am

#1690638

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: 1004494 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