Complex search Functionality with joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex search Functionality with joins

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-19 : 17:39:00

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 Candy" 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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-19 : 18:24:16

I suspect that you mis-typed your expected results for "cooking candy." Other than that, here is one way:

--DECLARE @Val VARCHAR(50) = 'sugar';
DECLARE @Val VARCHAR(50) = 'Cooking Candy';

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 DISTINCT
	Cooking.*
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 + '%';

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-19 : 18:47:50

Hi Lamprey,
a small mistake i made and here is the correctd text

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

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

but on your sample query yo are trying to pull cooling.*. but we are missing Provider from cooking_details table, in need that value as well.

Any suggestions please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-19 : 18:55:29

I got what to do and here is latest query


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 + '%';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources