Comparing data in two different tables to find missing records

[jennifersalcido]

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

[rwoeke]

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!

SELECT *
FROM inventory i
left outer join INV_CROSS_REF inv
on i.item_number = inv.item_number
WHERE inv.item_number IS NULL
and scancode = 4

[jennifersalcido]

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.

[rwoeke]

Change the

and scantype = 4 to or scantype = 4

[ck9663]

Try:

SELECT i.*
FROM inventory i
left outer join INV_CROSS_REF inv on i.item_number = inv.item_number
WHERE inv.item_number IS NULL or (inv.item_number IS not NULL and i.type = 5)

Happy coding

-- CK

[jennifersalcido]

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

[ck9663]

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

SELECT i.*
FROM inventory i
left outer join INV_CROSS_REF inv on i.item_number = inv.item_number
WHERE (inv.item_number IS NULL AND i.type <> 4) or (inv.item_number IS not NULL and i.type = 5)

-- CK

[jennifersalcido]

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?

[ck9663]

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

[Delerna]

Does this work for you

SELECT a.*
FROM Inventory a
LEFT JOIN (SELECT Item_Number FROM Inv_Cross_Ref WHERE type=4)i
  on a.Item_Number=i.Item_Number
WHERE i.Item_Number is null
 

[jennifersalcido]

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

INVENTORY

Item_Number     Description
100000          Item with both scancode and mfr pn
200000          Item with neither scancode nor mfr pn
300000          Item with mfr pn only
400000          Item with scancode only
 

INV_CROSS_REF

DATA           TYPE     SEQUENCE_NUMBER     ITEM_NUMBER
01234567890      4       1                  100000
09876543210      4       2                  100000
MI-543211        5       1                  100000
MI-123451        5       1                  300000
12345678901      4       1                  400000
 

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!

[ck9663]

Delerna's suggestion would work...

I extended that code to:

select * from inventory
select * from iNV_CROSS_REF
 
select inventory.item_number, description from inventory
   left join (select * from iNV_CROSS_REF where iNV_CROSS_REF.type = 4) fours on inventory.item_number = fours.item_number
   left join (select * from iNV_CROSS_REF where iNV_CROSS_REF.type = 5) fives on inventory.item_number = fives.item_number
where fours.item_number is null 

You can add where clause for the "fives'

-- CK