テーブルA,BをUNION ALLして結合したテーブルに対して、取得したAを条件にしてNOT EXISTSしたい

Question

前提・実現したいこと

マスタとなる取引担当テーブル（TRANS_TBL）と
それに変更を加える際に必要な情報が入った申込取引担当テーブル(APPLI_TRANS_TBL）があります。

APPLI_TRANS_TBLの部署コードまでと一致する、かつ削除フラグが0（有効）なTRANS_TBLを取得し、
APPLI_TRANS_TBLと結合します。

　
■TRANS_TBL（マスタ）

地域コード	会社コード	部署コード	担当者コード	削除フラグ
1000	200	10	01	0
1000	200	10	02	0
1000	200	20	03	0

　
■APPLI_TRANS_TBL（申込内容）

申込番号	地域コード	会社コード	部署コード	担当者コード	削除フラグ
2018102101	1000	200	10	01	1
2018102101	1000	200	10	03	0

　
■結合後

地域コード	会社コード	部署コード	担当者コード	削除フラグ
1000	200	10	01	0
1000	200	10	02	0
1000	200	10	01	1
1000	200	10	03	0

この結合したものから、
・削除フラグが「1」のもの
・↑と担当者コードまで一致するTRANS_TBL（論理削除されるマスタ）
を除外したいです。

　
■期待結果

地域コード	会社コード	部署コード	担当者コード	削除フラグ
1000	200	10	02	0
1000	200	10	03	0

試したこと

SELECT
B.地域コード
B.会社コード
B.部署コード
B.担当者コード
B.削除フラグ
FROM
TRANS_TBL AS B
WHERE
B.地域コード = '1000'
B.会社コード = '200
B.部署コード = '10'
B.削除フラグ = '0'

UNON ALL

SELECT 
A.地域コード
A.会社コード
A.部署コード
A.担当者コード
A.削除フラグ
FROM
APPLI_TRANS_TBL AS A
WHERE
A.申込番号 '2018102101'
A.地域コード = '1000'
A.会社コード = '200
A.部署コード = '10'
AND NOT EXISTS(
SELECT *
FROM TRANS_TBL AS BB
A.地域コード = BB.地域コード
A.会社コード = BB.会社コード
A.部署コード = BB.部署コード
A.担当者コード = BB.担当者コード
)

　
■結果

地域コード	会社コード	部署コード	担当者コード	削除フラグ
1000	200	10	01	0
1000	200	10	02	0
1000	200	10	03	0

・削除フラグが「1」のAPPLI_TRANS_TBLは除外される
・論理削除されるTRANS_TBLが除外されない

背景

変更内容のファイル出力の条件分岐に必要となるSQLになります。
部署単位で見た際、削除フラグが0（有効）のものが存在しない場合にファイル出力をします。
ファイル出力→マスタ変更処理の順で行われるため、
一度マスタと申込内容を結合し、その後、削除申込と削除されるマスタを除外して、
レコード存在チェックをしています。

補足情報（FW/ツールのバージョンなど）

企業向けの一般的でないSQLを使用していますが、
記載方法に関してはほぼMySQLなどのメジャーなものと変わらない認識です。

　
初心者ゆえ分かりづらい表現が多いかもしれません、申し訳ございません。
お手数ですが、何卒よろしくお願い致します。

（追記）
説明不足ですみません。
存在チェック自体は部署コード単位なのですが、
除外する、しないは担当者コード単位で行いたいのです。

また、同じ申込番号で、
レコードは分かれますが、追加と削除どちらも行うことが出来るため
（この場合、
担当者コード01が削除、03が追加）
一度担当者コード含めた主キーを取得して、
削除されるマスタ・削除申込を除外し
削除されないマスタ、追加される申込を残したい所存です。
何卒よろしくお願い致します。

Answer 1

UNION する前に Not Existsする方が効率的です。

  SELECT  地域コード, 会社コード, 部署コード, 担当者コード, 削除フラグ
  FROM TRANS_TBL
  WHERE 地域コード='1000' and 会社コード='200' and 部署コード='10' and 削除フラグ='0'
    and not exists(
         select 1 from APPLI_TRANS_TBL where 地域コード='1000' and 会社コード='200' and 部署コード='10' and 削除フラグ='1'
        )
UNON ALL
  SELECT  地域コード, 会社コード, 部署コード, 担当者コード, 削除フラグ
  FROM APPLI_TRANS_TBL
  WHERE 申込番号='2018102101' and 地域コード='1000' and 会社コード='200' and 部署コード='10' and 削除フラグ='0'
    and not exists(
         select 1 from TRANS_TBL where 地域コード='1000' and 会社コード='200' and 部署コード='10'
       )

追記

変更内容のファイル出力の条件分岐に必要となるSQLになります。
部署単位で見た際、削除フラグが0（有効）のものが存在しない場合にファイル出力をします。

TRANS_TBLに存在しないAPPLI_TRANS_TBLの抽出という事なら以下で良いかと思います。
※TRANS_TBLのキーは地域コード、会社コード、部署コード、担当者コードだとして

  SELECT distinct 地域コード, 会社コード, 部署コード, 担当者コード
  FROM APPLI_TRANS_TBL as t1
  WHERE 削除フラグ='0'
    and not exists(
         select 1 from TRANS_TBL where 地域コード=t1.地域コード and 会社コード=t1.会社コード and 部署コード=t1.部署コード and 担当者コード=t1.担当者コード
       )

Answer 2

すみません、本件自己解決致しました。
時間ができた際に方法追記できればと思います。

Answer 3

背景については理解できませんでした。

期待結果についてのみの回答です。

SELECT B.地域コード, B.会社コード, B.部署コード, B.担当者コード, B.削除フラグ
FROM TRANS_TBL AS B
WHERE EXISTS (SELECT 1 FROM APPLI_TRANS_TBL AS A
WHERE B.地域コード = A.地域コード
AND B.会社コード = A.会社コード
AND B.部署コード = A.部署コード)
AND NOT EXISTS(SELECT 1 FROM APPLI_TRANS_TBL AS A
WHERE B.地域コード = A.地域コード
AND B.会社コード = A.会社コード
AND B.部署コード = A.部署コード
AND B.担当者コード = A.担当者コード
AND A.削除フラグ = '1')
AND B.削除フラグ = '0'
UNION ALL
SELECT A.地域コード, A.会社コード, A.部署コード, A.担当者コード, A.削除フラグ
FROM APPLI_TRANS_TBL AS A
WHERE A.削除フラグ = '0'

Comments

[sazi]

SQLを```で括った方が見やすくなります。
その際、字下げなどで整形した方が良いかと。

Answer 4

select * from TRANS_TBL as A
where A.'担当者コード' not in (select B.'担当者コード' from TRANS_TBL as B where B.'削除フラグ' = 1)
  and A.'担当者コード' not in (select C.'担当者コード' from APPLI_TRANS_TBL as C where C.'削除フラグ' = 1)