比较两个不同表中的数据以查找丢失的记录 [英] Comparing data in two different tables to find missing records
问题描述
Hello All，
我正在使用两个包含库存数据的表：
1）INVENTORY包含item_number，description等这个数据相对简单，每个item_number有一个记录。
2）INV_CROSS_REF包含item_number，以及包含扫描码或制造商部件号的数据列。 type列指示数据列中的数据类型（4 = scancode，5 = mfr pn）。最后，因为软件允许每个item_number最多5个扫描码和2个mfr pn ...序列列包含数字1-5。因此，此表中每个item_number有多个记录。
我需要查找没有扫描码的记录（INV_CROSS_REF中的数据类型4）。但是，我不能只查询该表本身，因为它只包含具有数据的项（它不包含空值）。所以我需要将它与INVENTORY表进行比较，找到INVENTORY中但在INV_CROSS_REF 中不存在且数据类型为4记录的记录。
根据我在此论坛上发现的上一篇文章（谢谢！）我在那里中途。我可以使用以下查询找到INV_CROSS_REF中根本不存在的item_numbers：
SELECT *
FROM inventory
WHERE item_number不在（从inv_cross_ref中选择item_number）
但是，我需要添加下一个条件，以包含做在INV_CROSS_REF中存在的其他记录，但是没有类型4数据记录（或相反，只有类型5数据记录）。这些项目只有一个mfr pn，但没有扫描码记录。
提前谢谢!!
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!!
推荐答案
HI
试试这个 - 我现在无法测试它，但是我只是做了一个左外连接并添加了2个where子句。
$如果该项不在INV_CROSS_REF中，则b $ b inv.item_number应该为空。
检查它是否有效并让我知道！
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!
感谢您的回复！您的代码通过（只需将scancode更改为type），但不返回任何结果。我需要隔离存在于INVENTORY中但在INV_CROSS_REF中不存在类型= 4记录的项目编号。意思是，我需要的项目编号符合以下两个条件之一：
1）它们可能根本不存在于INV_CROSS_REF中
或
2）它们可能存在于INV_CROSS_REF中，但只有type = 5条记录。
如果从查询中删除最后一个WHERE子句（其中type = 4），我得到的项目符合上述标准1。如果我把那个条款放回去，我什么也得不到。但是我知道有数百个符合上述标准2的项目，我也需要这些项目。
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.
更改
和scantype = 4 to 或scantype = 4
这篇关于比较两个不同表中的数据以查找丢失的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
