获取表1中的行不匹配表2在多列上使用JOIN(Getting the Rows in Table 1 That does not Match Table 2 Using JOIN on Multipl

获取表1中的行不匹配表2在多列上使用JOIN(Getting the Rows in Table 1 That does not Match Table 2 Using JOIN on Multiple Columns)

嗨我有这样的查询：

SELECT TT2.whs_code, TT2.pdt_code, TT2.fresh_frozen_status, TT2.case_dt_yyyymmdd, TT2.qty_cases, TT2.qty_wt FROM #TempTable2 AS TT2 LEFT OUTER JOIN #TempTable AS TT1 ON TT1.whs_code = TT2.whs_code AND TT1.pdt_code = TT2.pdt_code AND TT1.fresh_frozen_status = TT2.fresh_frozen_status AND TT1.case_dt_yyyymmdd = TT2.case_dt_yyyymmdd

事情是我想在＃TempTable2中选择＃TempTable1中不存在的列

whs_code, pdt_code, fresh_frozen_status, case_dt_yyyymmdd

让我们说一组数据1,115G,FR,20160222在＃TempTable1中所以如果一组数据说必须选择1,115G,FR,20160223或必须选择1,115G,FZ,20160223或必须选择2,115G,FR,20160223 但不是1,115G,FR,20160222

Hi I have the Query Like this :

SELECT TT2.whs_code, TT2.pdt_code, TT2.fresh_frozen_status, TT2.case_dt_yyyymmdd, TT2.qty_cases, TT2.qty_wt FROM #TempTable2 AS TT2 LEFT OUTER JOIN #TempTable AS TT1 ON TT1.whs_code = TT2.whs_code AND TT1.pdt_code = TT2.pdt_code AND TT1.fresh_frozen_status = TT2.fresh_frozen_status AND TT1.case_dt_yyyymmdd = TT2.case_dt_yyyymmdd

The Thing is I want to select the columns in #TempTable2 That are not existed in #TempTable1 based on

whs_code, pdt_code, fresh_frozen_status, case_dt_yyyymmdd

means that lets say a set of data 1,115G,FR,20160222 is in #TempTable1 So If a set of data Say 1,115G,FR,20160223 has to be selected or 1,115G,FZ,20160223 has to be selected or 2,115G,FR,20160223 has to be selected but not 1,115G,FR,20160222

最满意答案

我会使用NOT EXISTS。

SELECT TT2.whs_code, TT2.pdt_code, TT2.fresh_frozen_status, TT2.case_dt_yyyymmdd, TT2.qty_cases, TT2.qty_wt FROM #TempTable2 AS TT2 WHERE NOT EXISTS ( SELECT TOP 1 * FROM #TempTable AS TT1 WHERE tt1.whs_code = tt2.whs_code AND tt1.pdt_code = tt2.pdt_code AND TT1.fresh_frozen_status = tt2.fresh_frozen_status AND TT1.case_dt_yyyymmdd = TT2.case_dt_yyyymmdd )

I would use NOT EXISTS.

SELECT TT2.whs_code, TT2.pdt_code, TT2.fresh_frozen_status, TT2.case_dt_yyyymmdd, TT2.qty_cases, TT2.qty_wt FROM #TempTable2 AS TT2 WHERE NOT EXISTS ( SELECT TOP 1 * FROM #TempTable AS TT1 WHERE tt1.whs_code = tt2.whs_code AND tt1.pdt_code = tt2.pdt_code AND TT1.fresh_frozen_status = tt2.fresh_frozen_status AND TT1.case_dt_yyyymmdd = TT2.case_dt_yyyymmdd )

更多推荐