获取表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_yyyymmddThe 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_yyyymmddmeans 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 )更多推荐
发布评论