need help.... - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

need help....

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-03-23 : 05:44:13

my query as follows
---------------------------------------------------------
select shop.shop_id,shop.shop_title from tbl_shops shop Inner Join @tbl_Programs prog on shop.program_id=prog.program_id
---------------------------------------------------------
tbl_shop_Request has the following data
shop_id shopperid
1 1
2 1
3 1
. .
. .
-----------------------
i have shopper_id in @shopper_id in my sp(this code is part of my sp)
I want to select all the shop_id from above selection which does not exists in tbl_shop_Requests and whose shopper_id = @shopperid
Let me cleare one thing is that i do not want to use 'not in' clause bcz is slow don my query. currently I am using the following Query to get the desired results.
-------------------------------------------------------------------------------
Declare @tbl_shops table(id numeric identity,shop_id numeric,shop_title varchar(500))
Insert into @tbl_shops(shop_id,shop_title)
select shop.shop_id,shop.shop_title
from tbl_shops shop Inner Join @tbl_Programs prog on shop.program_id=prog.program_id
*******************************************************************************************************
Declare @Shop_id numeric
Declare @Counter numeric
Declare Cur_RequestShops cursor forward_only for
select shop_id from @tbl_shops
Open Cur_RequestShops
fetch next from Cur_RequestShops into @Shop_Id
While @@Fetch_Status=0
Begin
Select @Counter=Count(*) from tbl_shop_requests where shop_id=@Shop_Id and shopper_id=@intShopperId
if @Counter<>0
Begin
Delete from @tbl_shops where shop_id=@Shop_id
End
fetch next from Cur_RequestShops into @Shop_Id
End
close Cur_RequestShops
Deallocate Cur_RequestShops
-------------------------------------------------------------------------------
But my query is slow done in this case.

Muhammad Saifullah

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-23 : 05:55:51

I Guess yo u can use left outer join for that matter..

Select Shop.ShopID From tbl_shops Left Outer Join tbl_shop_Requests On
Shop.ShopID = tbl_shop_Requests.ShopID
Where tbl_shop_Requests.ShopID Is null

These will give you all shopid from tbl_shops which are not ther ein the tbl_shop_Requests Table..

Sucess Comes to those who Believe in Beauty of their Dream..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 06:29:06

Can you do this ?


Declare @tbl_shops table
	(
		id 		numeric identity,
		shop_id 	numeric,
		shop_title 	varchar(500)
	)

insert into @tbl_shops(shop_id, shop_title)
	select 	shop.shop_id, shop.shop_title
	from 	tbl_shops shop Inner Join @tbl_programs prog 
	on 	shop.program_id	= prog.program_id
	and	not exists (select * from tbl_shop_requests x where x.shop_id = shop.shop_id)

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 06:31:45

just for continuity this thread originated from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63596

mrsaif
Starting Member

43 Posts

Posted - 2006-03-23 : 06:41:47

Thanks khtan it realy works for me. But one thing what i want to know is the difference betewwn 'in' clause and 'exits' clause.

Muhammad Saifullah

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-23 : 06:45:00

Read this
http://groups.google.de/group/microsoft.public.sqlserver.programming/tree/browse_frm/thread/1c12caa50923d3d5/f86de13e0ed65a37?rnum=1&hl=de&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.programming%2Fbrowse_frm%2Fthread%2F1c12caa50923d3d5%2Fe96cf1972f400ad9%3Fhl%3Dde%26lr%3D%26ie%3DUTF-8%26rnum%3D48%26prev%3D%2Fgroups%3Fq%3Dsql%2Bserver%2Bdifference%2Bnot%2Bin%2Bnot%2Bexists%26start%3D40%26hl%3Dde%26lr%3D%26ie%3DUTF-8%26selm%3Dumy7hwRaBHA.1916%2540tkmsftngp05%26rnum%3D48%26#doc_f530df34d5afe639

Madhivanan

Failing to plan is Planning to fail