Need help with 'Not Exists' query

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

SQL Server Development (2000)

Need help with 'Not Exists' query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-21 : 07:07:04

Hi

I have following tables:
Orders (orderID..)
NewOrders(orderID..)

Now I want to insert OrderIDs from Orders into a new Table where the OrderID does not exist in NewOrders. I am doing it the following way:

declare @NewTable table (OrderID int)

insert into @NewTable (OrderID) select orderid from Orders oo

inner join
Users c on oo.username = c.username

where c.OpNumber > '' AND
NOT Exists (select vo.OrderID from Orders vo, NewOrders voo where vo.orderid = voo.orderid)

But unfortunately, this query is not running properly, it does not give me the new OrderIDs that are not present in NewOrders

Hope you can help
Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-21 : 07:16:33

Use a left join in your sub query..

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-21 : 07:42:57

I have tried this as well:
NOT Exists (select vo.OrderID from Orders vo, left join NewOrders voo on vo.orderid = voo.orderid)

But still does not work:

I have following data:


Orders
1
2
3


NewOrders
1
3

And when I run the query, it should insert '2' into NewOrders as well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-21 : 07:52:15

try this

declare @NewTable table (OrderID int)

insert into @NewTable (OrderID) 
select 	oo.orderid 
from 	Orders oo inner join Users c 
	on 	oo.username = c.username
where 	c.OpNumber > '' 
AND	NOT Exists (select voo.orderid from NewOrders voo where voo.orderid = oo.orderid)

----------------------------------
'KH'
It is inevitable

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-21 : 08:02:51

Thanks khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources