Comparing data in two different tables to find missing records

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

re: Comparing data in two different tables to find missing records

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!

re: Comparing data in two different tables to find missing records

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.

re: Comparing data in two different tables to find missing records

Change the

and scantype = 4 to or scantype = 4

re: Comparing data in two different tables to find missing records

Try:

Happy coding

-- CK

re: Comparing data in two different tables to find missing records

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

re: Comparing data in two different tables to find missing records

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

-- CK

re: Comparing data in two different tables to find missing records

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?

re: Comparing data in two different tables to find missing records

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

re: Comparing data in two different tables to find missing records

Does this work for you

re: Comparing data in two different tables to find missing records

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

INVENTORY
INV_CROSS_REF
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!

re: Comparing data in two different tables to find missing records

Delerna's suggestion would work...

I extended that code to:

You can add where clause for the "fives'

-- CK