SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいので

質問

SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいのですがヒントをいただけないでしょうか。
下に例を作ってみました。（テキストに貼りなおしてもらうと見易くなると思います）
＜Aテーブル＞
A1 A2 A3 A4 A5
------- ------- ------- ------- ----------
XXXXXXX XX1 ABC 3 2009/05/08
WWCWWCW WW2 CCB 1 2008/03/21
DDDDDDD DD1 JPN 5 2007/08/08
GGGGGGG GX9 SOX 2 1977/01/04
FFFFFFF USJ NPB 3 2001/09/11

＜Bテーブル＞
B1 B2 B3 B4 B5
------- ------- ------- ------- ----------
XXXXXXX XX1 ibicha oshimu 2002/07/05
XXXXXXX XX1 takeshi okada 2005/07/15
XXXXXXX XX1 kamo shu 1857/09/25
WWCWWCW WW2 wao wao 2008/10/22
DDDDDDD DD1 ui hhh 2006/06/30
DDDDDDD DD1 jojoj x5xx 1999/09/09
DDDDDDD DD1 momo hara 2005/03/07
DDDDDDD DD1 itai u- 2003/12/22
DDDDDDD DD1 komano goal 2007/04/26
GGGGGGG GX9 dame record 2009/11/14
FFFFFFF USJ iki tai 1995/08/15
FFFFFFF USJ sstebu spi 2004/01/05
FFFFFFF USJ bb pp 2009/08/23

A1とB1は主キー、A2とB2は副キーです。
Aテーブルの「A4」カラムにはBテーブルのレコード数を持っています。（主キー、副キーが同じものの）
しかし、よくよく見るとAテーブルの「GGGGGGG」のA4には「2」のはずなのにBテーブルには実際にレコードは１つしかありません。
こういう状態になってしまっているのを割り出したいのですがどのようにA4とBテーブルで数があってないものを抽出できるでしょうか。
よろしくお願いいたします。

回答No.4

nda23 · ベストアンサー

SELECT A.* FROM Ａテーブル A
INNER JOIN
TABLE(SELECT B1,B2,COUNT(*) SU
FROM Ｂテーブル GROUP BY B1,B2) B
ON A.A1=B.B1 AND A.A2=B.B2
AND A.A4<>B.SU

または

WITH B(B1,B2,SU) AS
(SELECT B1,B2,COUNT(*) SU
FROM Ｂテーブル GROUP BY B1,B2)
SELECT A.* FROM Ａテーブル A
INNER JOIN B ON A.A1=B.B1
AND A.A2=B.B2 AND A.A4<>B.SU

こんなのでどうでしょうか？
後者の方が標準的（他のDBシステムでも
使える可能性が高い）かと思います。

お礼

後者のものが使うこと出来ました
大変参考になりました
ありがとうございます！

回答No.3

yamada_g

No.1です。
自分への返信の後半部分はNo.2さん宛のものですかね？
DB2は触ったことがないのですが、MINUSの代わりに EXCEPT を使えばよさそうです。

SELECT
A1,A2,A4
FROM Aテーブル
EXCEPT
SELECT
B1,B2,COUNT(*)
FROM Bテーブル
GROUP BY B1,B2

お礼

ありがとうございます　助かりました！

回答No.2

nda23

考え方を整理します。
(1)ＢテーブルのB1、B2をキーとして件数を取得
(2)Ａテーブル上で同じキーを持ち、かつ(1)の数と
　　A4の値が異なるものを抽出

SELECT A.* FROM Ａテーブル A
INNER JOIN
(SELECT B1,B2,COUNT(*) SU
FROM Ｂテーブル GROUP BY B1,B2) B
ON A.A1=B.B1 AND A.A2=B.B2
AND A.A4<>B.SU

お礼

回答ありがとうございます。
Oracleで上記のような結果を得たいと思っていた次第なのですが、私が間違いをしておりました。
DB2 9.5が大元になっており一部のデータをOracleへコピーしています。
Oracle側はBテーブルを持っていないためOracleではそもそも比較しようがないことがわかりました。
そこで大元のDB2でやってみたのですが、どうやらせっかく御教示いただいたSQLがエラーになってしまします。

意味がよくわからなくて大変申し訳ないのですが、ご回答いただいたSQLの構文でJOINの後に“(”があると
無効な識別子みたいなエラーを吐き出します。
規格とかが根本から違うのでしょうか？

本当にポカミスですみません。。。

回答No.1

yamada_g

記載の内容が全てで難しいこと考えなくていいなら、minusを使ってはいかがでしょうか？

SELECT
A1,A2,A4
FROM Aテーブル
MINUS
SELECT
B1,B2,COUNT(*)
FROM Bテーブル
GROUP BY B1,B2

これでBテーブルの件数とA4の値が一致しないAテーブルのレコード(全項目ではないですが）が抽出できます。

余談ですが、B1は主キーとは言わないです。主キーとはテーブル内の一意のレコードを特定できるものを言います。
あと、Oracleのバージョンも書いた方がいいと思います。バージョンによって使用できるSQLも違ってきますので。

お礼

回答ありがとうございます。
Oracleで上記のような結果を得たいと思っていた次第なのですが、私が間違いをしておりました。
DB2 9.5が大元になっており一部のデータをOracleへコピーしています。
Oracle側はBテーブルを持っていないためOracleではそもそも比較しようがないことがわかりました。
そこで大元のDB2でやってみたのですが、どうやらせっかく御教示いただいたSQLがエラーになってしまします。

意味がよくわからなくて大変申し訳ないのですが、ご回答いただいたSQLの構文でJOINの後に“(”があると
無効な識別子みたいなエラーを吐き出します。
規格とかが根本から違うのでしょうか？

本当にポカミスですみません。。。