从一个表中排除另一个表 [英] Excluding one table from another

问题描述

我早些时候向我们提出了这个问题的变化并得到了一个很好的答案，但问题是

编写得不好。



我是在VB_2013工作

我有2个表Table_Invoice，Table_Inventory

Table_Invoice字段.. Custcode，DrawingNumber，DrawingRevision

Table_Inventory字段.. CustCode，DrawingNumber ，DrawingRevision



Table_Invoice拥有客户所下订单年份的所有记录。

许多订单已经重复使用相同的修订版，许多订单已经进行了新的修订。我需要使用DrawingRevision创建一个DrawingNumbers列表，该列表尚未包含在表库存中



示例.....所有项目都在Table_Invoice中多次

Table_Invoice DrawingNumber 868-1001 DrawingRevision A

Table_Invoice DrawingNumber 868-1001 DrawingRevision A

Table_Invoice DrawingNumber 868-1001 DrawingRevision B

Table_Invoice DrawingNumber 868-1001 DrawingRevision C

Table_Invoice DrawingNumber 868-1001 DrawingRevision D

Table_Invoice DrawingNumber 868-1001 DrawingRevision D

Table_Invoice DrawingNumber 868-1001 DrawingRevision E $>
Table_Invoice DrawingNumber 868-1001 DrawingRevision E



所有商品只列出一次

Table_Inventory DrawingNumber 868- 1001 DrawingRevision B

Table_Inventory DrawingNumber 868-1001 DrawingRevision C





我试图创建一个查询，创建一个显示t的列表对于不在库存中的商品，只有DrawingRevision的DrawingNumber一次



Table_Invoice DrawingNumber 868-1001 DrawingRevision A（868-1001 Rev A）

Table_Invoice DrawingNumber 868-1001 DrawingRevision A（868-1001 Rev D）

Table_Invoice DrawingNumber 868-1001 DrawingRevision A（868-1001 Rev E）



我是这个论坛的新手。我的第一个问题得到了非常快速和正确的回答

但是有些我失去了答案却无法回答答案

再次感谢

Mike

解决方案

如果我正确理解了这个问题，可能就像

  SELECT   DISTINCT  
 tin.DrawingNumber，
 tin.DrawingRevision 
  FROM  Table_Invoice tin 
  WHERE   NOT   EXISTS （ SELECT   1  
  FROM  Table_Inventory tiv 
  WHERE  tiv.DrawingNUmbre = tin.DrawingNumber，
  AND  tiv.DrawingRevision = tin .DrawingRevision）

I asked a variation us this question earlier and got a great answer but the question
was poorly written.

I'm working in VB_2013
I have 2 tables Table_Invoice , Table_Inventory
Table_Invoice Fields.. Custcode, DrawingNumber,DrawingRevision
Table_Inventory Fields.. CustCode, DrawingNumber,DrawingRevision

Table_Invoice has all the records thru the years of every order the customer placed.
Many orders have been repeated with the same revisions, and many orders have been placed with new revisions. I need to create a list of DrawingNumbers with DrawingRevision that are not already in Table Inventory

Example..... All items are in Table_Invoice multiple times
Table_Invoice DrawingNumber 868-1001 DrawingRevision A
Table_Invoice DrawingNumber 868-1001 DrawingRevision A
Table_Invoice DrawingNumber 868-1001 DrawingRevision B
Table_Invoice DrawingNumber 868-1001 DrawingRevision C
Table_Invoice DrawingNumber 868-1001 DrawingRevision D
Table_Invoice DrawingNumber 868-1001 DrawingRevision D
Table_Invoice DrawingNumber 868-1001 DrawingRevision E
Table_Invoice DrawingNumber 868-1001 DrawingRevision E

All items are only list once
Table_Inventory DrawingNumber 868-1001 DrawingRevision B
Table_Inventory DrawingNumber 868-1001 DrawingRevision C


I trying to create a query that will create a list showing the DrawingNumber with DrawingRevision only once for item that is not in inventory already

Table_Invoice DrawingNumber 868-1001 DrawingRevision A ( 868-1001 Rev A )
Table_Invoice DrawingNumber 868-1001 DrawingRevision A ( 868-1001 Rev D )
Table_Invoice DrawingNumber 868-1001 DrawingRevision A ( 868-1001 Rev E )

I'm new to this forum. My first question was answered very quickly and correctly
but some how I lost the answer and could not respond to the answer
Thanks again
Mike

解决方案

If I understood the question correctly, perhaps something like

SELECT DISTINCT
       tin.DrawingNumber,
       tin.DrawingRevision
FROM Table_Invoice tin
WHERE NOT EXISTS (SELECT 1
                  FROM Table_Inventory tiv
                  WHERE tiv.DrawingNUmbre   = tin.DrawingNumber,
                  AND   tiv.DrawingRevision = tin.DrawingRevision)

这篇关于从一个表中排除另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！