sql join - 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

General SQL Server Forums

New to SQL Server Programming

sql join

Author

Topic

mr_dayal
Starting Member

37 Posts

Posted - 2008-07-02 : 09:26:29

select vitemid from mainstock
minus
select vitemid from itemmaster

what's the query in sql server

Mr Dayal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:32:46

Are you using SQL Server 2000 or 2005?

You need to read about Not exists or EXCEPT

Madhivanan

Failing to plan is Planning to fail

mr_dayal
Starting Member

37 Posts

Posted - 2008-07-02 : 09:39:43

no actually I am shifting from oracle to sqlserver 2005..

Currently I am using sqlserver 2005

Mr Dayal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:50:15

select vitemid from mainstock
except
select vitemid from itemmaster

Madhivanan

Failing to plan is Planning to fail

mr_dayal
Starting Member

37 Posts

Posted - 2008-07-02 : 09:53:50

its' throwing errors..

Actually I want to retrive only those records from vitemmaster
which does'nt exists in mainstock

Mr Dayal

mr_dayal
Starting Member

37 Posts

Posted - 2008-07-02 : 09:58:25

in other words I want to retrive all the rows from vitemmaster which r not in mainstock.

mainstock table has 1782 rows and vitemmaster table has 1418

Mr Dayal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:59:24

select vitemid from vitemmaster where vitemid not in
(
select vitemid from mainstock

)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 13:30:23

or use left join

select vitemid
from itemmaster i
left join mainstock m
on m.vitemid=i.vitemid
where m.v is null

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-02 : 14:47:31

The left join will be faster so that might be the better option.

Dallr

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 14:51:16

quote:
Originally posted by visakh16

or use left join

select vitemid
from itemmaster i
left join mainstock m
on m.vitemid=i.vitemid
where m.vitemid is null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources