Select not in query - 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 2005 Forums

Transact-SQL (2005)

Select not in query

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-22 : 13:01:03

OK here is an example or the query I'm running:

SELECT UPC
FROM Items
WHERE (UPC NOT IN
(SELECT UPC
FROM ItemsSD
WHERE (UPC = '5029365901420') OR
(UPC = '842977090147')))

I'm looking for which of those UPC's isn't in the ItemsSD database. What this does is shows all the UPC's that aren't in the ItemsSD database (there's a lot). I have around 1,000 UPC's to check to find what ones arn't in this database. Any help would be great.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:11:45

SELECT * FROM Items WHERE UPS NOT IN (SELECT UPC FROM ItemsSD)

is one of MANY Ways

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 13:25:51

Ever since I discovered "NOT EXISTS", I have been a fan. So here is another way using NOT EXISTS:


SELECT UPC 
FROM Items i 
WHERE NOT EXISTS (SELECT * FROM ItemsSD d WHERE d.UPC = i.UPC);

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:28:46

SELECT UPC FROM Items
EXCEPT
SELECT UPC FROM ItemsSD

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:30:45

[code]
SELECT i.UPC
FROM Items i
LEFT JOIN ItemsSD d
ON i.UPC = d.UPC
WHERE d.UPC IS NULL
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 13:33:44

Ok, ok, I give up

Brett: 3
Sunita: 1

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:36:35

oh come on..I'm sure you can come up with a cte to do this as well

what else?

Brett

EDIT: Ever go the park in Stamford that has the Ice Rink...my Daughter just had a synchro skating comp there...

Coming up from Jersey on a Sunday (over the GW) 40 minutes..going home at 6PM..2 hours

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 13:57:08

The joys of I-95!! I will say no more

And to keep this posting relevant (so the sysadmins don't ban us from the forum)

SELECT
	i.UPC
FROM
	Items i
	OUTER APPLY
	( SELECT UPC FROM ItemsSD j WHERE j.UPC = i.UPC) j
WHERE
	j.UPC IS NULL

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 14:30:30

[CODE]
SELECT UPC FROM (
SELECT mySource, UPC FROM (
SELECT 'I' AS mySource, UPC FROM Items
UNION ALL
SELECT 'SD' AS mySource, UPC FROM ItemsSD
) AS XXX
GROUP BY UPC
HAVING COUNT(*) = 1) AS XYZ
WHERE mySource = 'SD'
[/CODE]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-22 : 14:55:27

Yep you're missing I need it to show me which of the two aren't in the ItemsSD database:

SELECT UPC
FROM Items
WHERE (UPC NOT IN
(SELECT UPC
FROM ItemsSD
WHERE (UPC = '5029365901420') OR
(UPC = '842977090147')))

Got it to work using this:

SELECT UPC, StockQty, UsedQty, Attribute2
FROM Items
WHERE (UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '5029365901420') OR
(UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '842977090147')

But it a large drain on SQL sense I'm looking for over 500 UPC's.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 14:59:53

What we gave you shows you ALL of the UPC's in Items that are not in ItemsSD

What is it you are exactly looking to do?

Where do you come up with this 500 number?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-22 : 17:18:19

quote:
Originally posted by X002548

What we gave you shows you ALL of the UPC's in Items that are not in ItemsSD

What is it you are exactly looking to do?

Where do you come up with this 500 number?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

I need it to search for certain UPc's and tell what ones aren't in the ItemsSD database. Otherwise I have to go through over 65,000 UOC's.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-22 : 18:28:53

Can you post sample data and expected output to illustrate your requirement?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-23 : 11:32:31

quote:
Originally posted by Lamprey

Can you post sample data and expected output to illustrate your requirement?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Yep I did:

SELECT UPC, StockQty, UsedQty, Attribute2
FROM Items
WHERE (UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '5029365901420') OR
(UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '842977090147')

That will show the result of:
upc: 5029365901420 isn't in the itemsSD database, but it kills sql. I didn't know if there was any easier way to word it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-23 : 11:44:37

quote:
Originally posted by taunt

quote:
Originally posted by Lamprey

Can you post sample data and expected output to illustrate your requirement?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Yep I did:

SELECT UPC, StockQty, UsedQty, Attribute2
FROM Items
WHERE (UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '5029365901420') OR
(UPC NOT IN
(SELECT UPC
FROM ItemsSD)) AND (UPC = '842977090147')

That will show the result of:
upc: 5029365901420 isn't in the itemsSD database, but it kills sql. I didn't know if there was any easier way to word it.

No you didn't. I assume you didn't read the link I posted. So, please read that and then post DDL, DML and expected output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources