asked on

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.

SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gpsdh

ASKER

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

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...

gpsdh

ASKER

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!

Saurabh Singh Teotia

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...

Scott Pletcher

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.

Saurabh Singh Teotia

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..

Scott Pletcher

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.