By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,952 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,952 IT Pros & Developers. It's quick & easy.

Comparing data in two different tables to find missing records

P: 5
Hello All,

I am working with two tables that contain inventory data:

1) INVENTORY contains item_number, description, etc. This data is relatively straight-forward, one record per item_number.

2) INV_CROSS_REF contains item_number, and a data column containing either a scancode or a manufacturer part number. The type column indicates what type of data is in the data column (4=scancode, 5=mfr pn). Finally, because the software allows up to 5 scancodes and 2 mfr pn's per item_number... the sequence column contains a number 1-5. Therefore, there are multiple records per item_number in this table.

I need to find records that do not have a scancode (data type 4 in INV_CROSS_REF). However, I cannot just query that table by itself, because it only contains items that have data (it does not contain null values). So I need to compare it to the INVENTORY table, and find the records that are in INVENTORY but do not exist in INV_CROSS_REF with a data type 4 record.

Based on a previous posting I found on this forum (thank you!) I am half-way there. I can find the item_numbers that do not exist at all in INV_CROSS_REF using the following query:

SELECT *
FROM inventory
WHERE item_number not in (select item_number from inv_cross_ref)

However, I need to add the next condition, to include other records that do exist in INV_CROSS_REF, but without a type 4 data record (or conversely, only with a type 5 data record). These would be items that only have a mfr pn, but no scancode record(s).

Thank you in advance!!
Aug 18 '08 #1
Share this Question
Share on Google+
11 Replies


rwoeke
P: 11
HI

Try this - I cant test it now, but what im just doing a left outer join and adding 2 where clauses.

inv.item_number should be null if that Item is not in INV_CROSS_REF..

Check if it works and let me know!

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM inventory i
  3. left outer join INV_CROSS_REF inv
  4. on i.item_number = inv.item_number
  5. WHERE inv.item_number IS NULL
  6. and scancode = 4
Aug 19 '08 #2

P: 5
Thanks for the reply! Your code passes (just had to change scancode to type), but returns no results. I need to isolate item numbers that exist in INVENTORY but do not exist in INV_CROSS_REF with a type=4 record. Meaning, the item numbers I need will meet one of two conditions:

1) They may not exist in INV_CROSS_REF at all
or
2) They may exist in INV_CROSS_REF but only with type=5 records.

If I remove the last WHERE clause from your query (where type=4), I get items that meet criteria 1 above. If I put that where clause back in I get nothing. However I know there are hundreds of items that meet criteria 2 above and I need those too.
Aug 19 '08 #3

rwoeke
P: 11
Change the

and scantype = 4 to or scantype = 4
Aug 19 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Try:

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*
  2. FROM inventory i
  3. left outer join INV_CROSS_REF inv on i.item_number = inv.item_number
  4. WHERE inv.item_number IS NULL or (inv.item_number IS not NULL and i.type = 5)
Happy coding

-- CK
Aug 19 '08 #5

P: 5
Both of these suggestions yield results (I had already tried the first suggestion on my own trying to figure it out today). However, it is pulling too much. Here's the problem ... there are item numbers in INV_CROSS_REF that have both type 4 and type 5 records. I need to exclude those item numbers. I need items that ONLY have type 5 records (with no type 4 records) as well as any that are null (this second part is working great).

Thanks again for the help everyone. I am getting closer ...
Aug 19 '08 #6

ck9663
Expert 2.5K+
P: 2,878
On this code, the following will be included:

1. Rows in INVENTORY but NOT IN INV_CROSS_REF and type <> 4
2. Rows in INVENTORY that ARE ALSO IN INV_CROSS_REF and type = 5

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*
  2. FROM inventory i
  3. left outer join INV_CROSS_REF inv on i.item_number = inv.item_number
  4. WHERE (inv.item_number IS NULL AND i.type <> 4) or (inv.item_number IS not NULL and i.type = 5)
-- CK
Aug 20 '08 #7

P: 5
Still no dice. So close though. Example, item 123456 exists twice in INV_CROSS_REF. One row with a type=4 (scancode) record and another row with a type=5 (mfr pn) record. I need to exclude this item number altogether from the report because I am only looking for items that have NO scancode, but it is still being pulled with this code because a row does exist with a type=5 record. I tried flipping the filters around but either way this item is pulled because a row exists that satisfies either condition (<>4 or <>5).

I am starting to think this may not be possible?
Aug 22 '08 #8

ck9663
Expert 2.5K+
P: 2,878
Still no dice. So close though. Example, item 123456 exists twice in INV_CROSS_REF. One row with a type=4 (scancode) record and another row with a type=5 (mfr pn) record. I need to exclude this item number altogether from the report because I am only looking for items that have NO scancode, but it is still being pulled with this code because a row does exist with a type=5 record. I tried flipping the filters around but either way this item is pulled because a row exists that satisfies either condition (<>4 or <>5).

I am starting to think this may not be possible?
So you have records with type = 1,2,3,4,5?

Or you just have type=4 which should have a paired record type=5 and you're trying to find those record with type=5 but does not have type=4?

Where's the type field? in INVENTORY or INV_CROSS_REF ? This would be easier if you post some sample data with these complications and which record you want excluded and which one you want included.

-- CK
Aug 22 '08 #9

Delerna
Expert 100+
P: 1,134
Does this work for you
Expand|Select|Wrap|Line Numbers
  1. SELECT a.*
  2. FROM Inventory a
  3. LEFT JOIN (SELECT Item_Number FROM Inv_Cross_Ref WHERE type=4)i
  4.   on a.Item_Number=i.Item_Number
  5. WHERE i.Item_Number is null
  6.  
Aug 23 '08 #10

P: 5
The type field is in INV_CROSS_REF. Here is a sample from both tables:

INVENTORY
Expand|Select|Wrap|Line Numbers
  1. Item_Number     Description
  2. 100000          Item with both scancode and mfr pn
  3. 200000          Item with neither scancode nor mfr pn
  4. 300000          Item with mfr pn only
  5. 400000          Item with scancode only
  6.  
INV_CROSS_REF
Expand|Select|Wrap|Line Numbers
  1. DATA           TYPE     SEQUENCE_NUMBER     ITEM_NUMBER
  2. 01234567890      4       1                  100000
  3. 09876543210      4       2                  100000
  4. MI-543211        5       1                  100000
  5. MI-123451        5       1                  300000
  6. 12345678901      4       1                  400000
  7.  
In this example, item 100000 has two scancodes and one mfr pn. The fact that is has two scancodes is irrelavant to what I need, I just wanted to include that in the sample for "full disclosure". :)

From this sample, I would need items 200000 and 300000 returned from my query, because neither of them has a type 4 record in INV_CROSS_REF. With what I have so far, I am getting item 200000 in my result set, but I can't get it to return item 300000 without also pulling item 100000 which I don't want.

So how can I exclude any item_number that has a type=4 record in INV_CROSS_REF? Thanks so much for the patience and help everyone!
Aug 25 '08 #11

ck9663
Expert 2.5K+
P: 2,878
Delerna's suggestion would work...

I extended that code to:

Expand|Select|Wrap|Line Numbers
  1. select * from inventory
  2. select * from iNV_CROSS_REF
  3.  
  4. select inventory.item_number, description from inventory
  5.    left join (select * from iNV_CROSS_REF where iNV_CROSS_REF.type = 4) fours on inventory.item_number = fours.item_number
  6.    left join (select * from iNV_CROSS_REF where iNV_CROSS_REF.type = 5) fives on inventory.item_number = fives.item_number
  7. where fours.item_number is null 
You can add where clause for the "fives'

-- CK
Aug 25 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.