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 UPCFROM ItemsWHERE (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 |
|
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 |
|
X002548
Not Just a Number
15586 Posts |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-22 : 13:33:44
|
Ok, ok, I give up Brett: 3Sunita: 1 |
 |
|
X002548
Not Just a Number
15586 Posts |
|
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.UPCFROM Items i OUTER APPLY ( SELECT UPC FROM ItemsSD j WHERE j.UPC = i.UPC) jWHERE j.UPC IS NULL |
 |
|
X002548
Not Just a Number
15586 Posts |
|
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 UPCFROM ItemsWHERE (UPC NOT IN(SELECT UPCFROM ItemsSDWHERE (UPC = '5029365901420') OR(UPC = '842977090147')))Got it to work using this:SELECT UPC, StockQty, UsedQty, Attribute2FROM ItemsWHERE (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 |
|
taunt
Posting Yak Master
128 Posts |
|
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, Attribute2FROM ItemsWHERE (UPC NOT IN(SELECT UPCFROM ItemsSD)) AND (UPC = '5029365901420') OR(UPC NOT IN(SELECT UPCFROM 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, Attribute2FROM ItemsWHERE (UPC NOT IN(SELECT UPCFROM ItemsSD)) AND (UPC = '5029365901420') OR(UPC NOT IN(SELECT UPCFROM 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. |
 |
|
|