SQL- Find data not in the other table

  SELECT part_code, cost_ctr
             FROM dbo.inventory
             WHERE part_code = 'ZMD34006'
             
             SELECT part_code,cost_ctr
             FROM dbo.whsprmstr
              WHERE part_code = 'ZMD34006'

Open in new window


part_code	cost_ctr
ZMD34006	1
ZMD34006	4

Open in new window

part_code	cost_ctr
ZMD34006	1

Open in new window



I have some inventory records that are not set up in the whsprmstr table.  I want to find all of these where the cost_ctr parts are not setup in the whsprmstr.  Here is one part that I know has an inventory record in cost_ctr 4, but is not set up in the whsprmstr.
gpsdhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can run this query to do what you are looking for..

select tb1.part_code,tb1.cost_ctr
FROM dbo.whsprmstr tb1
left join dbo.inventory tb2 on tb1.part_code=tb2.part_code
where tb2.part_code is null

Open in new window


Saurabh...
0
gpsdhAuthor Commented:
SELECT part_code, cost_ctr
             FROM dbo.inventory
             WHERE part_code = 'ZUK90459'
             
             SELECT part_code,cost_ctr
             FROM dbo.whsprmstr
              WHERE part_code = 'ZUK90459'
             

Open in new window


When I ran your query I got this part as one of the results.  It shows no results for inventory and one for whsprmstr.  I am trying to find the results like the screenshot-5 where there is inventory for that cost center, but not a record of it in the whsprmstr.


Thanks!
Screenshot-5.png
Screenshot-6.png
0
Scott PletcherSenior DBACommented:
SELECT part_code, cost_ctr
             FROM dbo.inventory
             WHERE part_code = 'ZMD34006'
EXCEPT            
             SELECT part_code,cost_ctr
             FROM dbo.whsprmstr
              WHERE part_code = 'ZMD34006'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Saurabh Singh TeotiaCommented:
This is exactly what my query is doing my query is giving you all that data which exists in the whmpstr but don't exist in the other table...

If you notice i have applied a filter where tb2.part_code is null which means that record is not able to find in inventory table and that's why it will be null...

select tb1.part_code,tb1.cost_ctr
FROM dbo.whsprmstr tb1
left join dbo.inventory tb2 on tb1.part_code=tb2.part_code
where tb2.part_code is null

Open in new window


Can you share me the screenshot of the output of my query as it should give you all records which exists in one table and doesn't exists in another...
0
gpsdhAuthor Commented:
Saurabh yours gave me 13,000 results, but Scott's gave me the 428 I was looking for, because those did have inventory records and no corresponding whsprmstr record.


Thanks!
0
Saurabh Singh TeotiaCommented:
Gpsdh...

It gave you 13,000 records because for all that 13,000 you have entry only at whsprmstr not at inventory table..Pick up any record for your reference to validate my statement..

Saurabh...
0
Scott PletcherSenior DBACommented:
Saurabh:

You flipped the tables around, listing the whsprmstr table first.  Thus, your left outer join showed all rows in that table not in the inventory table, rather than the other way around.
0
Saurabh Singh TeotiaCommented:
Scott,

I know i flipped the table around but if you see this statement which is

where tb2.part_code is null

It will restrict the answer to only those which are mis matches..
0
Scott PletcherSenior DBACommented:
You've got it backwards, but whatever.   What is in TableA that is not in TableB is NOT the same as what is in TableB that is not in TableA.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.