Selecting not matching records

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)

Selecting not matching records

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-03-23 : 02:29:40

i have 2 tables like this
"tbl_Shop"
shop_id Title
1 a
2 b
3 c
4 d
5 e
6 f
--------------------------
"tbl_shop_request"
shop_id Title
3 c
5 e
------------------------
i want to get the all shop_ids which are not in tbl_shop_Request. remember i dont want to use 'in' clause because my data is very huge and i experianced that 'in' or 'not in' clause slow down the query. I want to use some kind of join to do the job for me. and Execute my query speedly.

Muhammad Saifullah

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 02:38:58

[code]select *
from tbl_shop s
where not exists (select * from tbl_shop_request x wehre x.shop_id = s.shop_id)[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-23 : 03:03:30

or

select s.* from tbl_shop s left join tbl_shop_request x on x.shop_id = s.shop_id
where x.shop_id is null

Madhivanan

Failing to plan is Planning to fail

mrsaif
Starting Member

43 Posts

Posted - 2006-03-23 : 04:16:05

Thanks madhivanan,
For me is dones not works.
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 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
-------------------------------------------------------------------------------

Muhammad Saifullah

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 05:47:09

continue over here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63608

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources