Value Vs Value - 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

Value Vs Value

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-03 : 08:35:03

These are the scripts I run. batchcartonid and caseid are the same thing.

the batchcartonid in pickdetail is orders that are picked
and the case from cload are the same orders that are loaded/ Pack complete.
I want to let it show me the batchcartonid that is not in the cload table.

select batchcartonid from pickdetail
select caseid from cload

Thank you!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-03 : 08:57:09

select batchcartonid from pickdetail
where batchcartonid not in (select caseid from cload)

Jim

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-09-03 : 09:02:26

NOT IN can give problems if caseid contains NULLs.

SELECT P.batchcartonid
FROM pickdetail P
WHERE NOT EXISTS
(
    SELECT *
    FROM cload C
    WHERE C.caseid = P.batchcartonid
)

-- or

SELECT P.batchcartonid
FROM pickdetail P
    LEFT JOIN cload C
        ON P.batchcartonid  = C.caseid
WHERE C.caseid IS NULL

-- etc

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-03 : 09:34:48

Thanksn Ifor
It works great...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-03 : 12:29:23

If you have 2005 you can use the EXCEPT operator:

select batchcartonid from pickdetail
EXCEPT
select caseid from cload

NOTE: I've not done a lot of testing, but in many cases EXCEPT seems to perform the fastest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources