SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex search Functionality with joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
303 Posts

Posted - 02/19/2014 :  17:39:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 02/19/2014 :  18:24:16  Show Profile  Reply with Quote
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 + '%';
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
303 Posts

Posted - 02/19/2014 :  18:47:50  Show Profile  Reply with Quote
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


Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
303 Posts

Posted - 02/19/2014 :  18:55:29  Show Profile  Reply with Quote
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 + '%';
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000