SQLでのデータチェック方法

質問

いつもお世話になっております。
SQLでのデータチェック方法について教えてください。

【売上テーブル】
日付、商品、代理店、金額
----------------------
5/1　消しゴム　AA商店　100
5/1　鉛筆　　　 BB商店　200
5/2　鉛筆　　　 ZZ商店　200
5/2　万年筆　　CC商店　150

【商品マスタテーブル】
品名
-----------
消しゴム
鉛筆

【代理店マスタテーブル】
代理店
-----------
AA商店
BB商店
CC商店

この中で各種マスタに存在しないデータを抽出したいです。

select * from 売上テーブル
where 商品　not in (select 品名 from 商品マスタ)
or 代理店　not in (select 代理店 from 代理店マスタ)

で抽出できるのはわかるのですが、
抽出結果に何故そのレコードが抽出されたのかをわかるようにしたいです。

日付、商品、代理店、金額　　理由
-------------------------------
5/2　鉛筆　　　 ZZ商店　200　代理店マスタに存在しない
5/2　万年筆　　CC商店　150　商品マスタに存在しない

このような感じで理由も追加することは可能でしょうか？
わがままですみません。

よろしくお願いします。

回答No.1

jhayashi · ベストアンサー

たとえば or使わないで UNION　使って

selct 日付,商品,代理店,金額,'代理店マスタに存在しない' from 売上テーブル
where 代理店　not in (select 代理店 from 代理店マスタ)
union
selct 日付,商品,代理店,金額,'商品マスタに存在しない' from 売上テーブル
where 商品　not in (select 品名 from 商品マスタ)

といったように別々の条件でselectしてしまう（代理店になく商品にも無いのは2行出てくる）

外部結合使って　外部結合先がNULLかどうかで判断してしまう
select 日付,商品,代理店,金額,nvl2(商品マスタ.品名,'商品マスタに存在しない',null),nvl2(代理店.代理店,'代理店マスタに存在しない',null)
from 売上テーブル,商品マスタ,代理店
where 売上テーブル.商品　= 商品マスタ.品名(+)
and 売上テーブル.代理店　= 代理店.代理店(+)
and (商品マスタ.品名 is null or 代理店.代理店 is null)

(+)つかわないでOUTER JOIN句つかって書いてもいいし
とりあえず nvl2で表記してみましたが　CASE WHENなどつかって1カラムで
'商品マスタと代理店マスタに存在しない'を加えて3通りの表示してあげるとか

nvl2(商品マスタ.品名,'商品マスタ','')||nvl2(代理店.代理店,'代理店マスタ','')||'に存在しない
とかメッセージ作っちゃうとか

お礼

なるほど！
チェック項目が増えそうなので、union案を採用させていただきました！
早々にご回答いただきましてありがとうございました。