sqlでNOT INでテーブルの値を比較

質問

テーブル
ID NUM TNNO
A 001 TN01
A 002 PC01
A 003 TN01
B 001 TNPC01
B 002 TN01
C 001 TN01
C 002 TN01

上記のようなテーブルがあります。
SQLで「NOT IN句」でテーブルの値の比較をやっているのですが、
結果が得られません。

IDが同じグループで、TNNOが違う行を取得したいのですが、
0件が返ってきます。

以下の結果を期待していますが、
ID NUM TNNO
A 001 TN01
A 002 PC01
A 003 TN01　：どちらでも
B 002 TN01
B 001 TNPC01
0件となります。

実行したクエリは以下ですが、
select * from tbl A
where (A.num,A.tnno) not in (select num,tnno from tbl B where A.id=B.id);

どこがいけないのでしょうか。理由がわかりますでしょうか。
NOT IN句を使うことがいけないのでしょうか。
取得するにはどう書き換えればよいのでしょうか。

よろしくお願いします。

回答No.3

yumi-access

重複している場合はgroup byを使用すると良いです。
SQLは色々な方法があり、これじゃないとダメというのはないです。
御自身でも色々調べて試してみてください。

select A.NUM, A.TNNO
from tbl as A
inner join tbl as B
on B.ID = A.ID and B.TNNO != A.TNNO
group by A.NUM, A.TNNO
order by A.NUM, A.TNNO

お礼

ありがとうございます。
調べてみます。

回答No.2

yumi-access

最近oracleから離れているからわからないですが
一般的にこんなJOINで出来そうですが如何でしょうか？

select A.ID, A.NUM, A.TNNO
from tbl as A
inner join tbl as B
on B.ID = A.ID and B.TNNO != A.TNNO
order by A.ID, A.NUM, A.TNNO

お礼

ありがとうございます。
ID NUM TNNO
A 001 TN01
A 002 PC01
A 002 PC01
A 003 TN01
B 001 TNPC01
B 002 TN01
という結果になりました。
A 002 PC01
が重複します。
たぶん、NUMが「001と003」で抽出されているかかと

回答No.1

bardfish

OracleってWHERE INで複数の列を指定できるのですか？
今はSQLServerをメインに開発をしていますが、IN句でそんな使い方をした事がないし、出来る事も知りませんでした。
そもそもそういう発想がない。

問題点を突き止める為にWHEREで記述されているSELECTを単独で実行し、その結果が思惑通りになっているか確認を。
そして、少数のサンプルから値を直接NOT INに記述して期待通りの結果になるか確認。

質問文にあるIN句では「'001','TN01','002','PC01','003','TN01'」と言う結果になると思います。コレをSELECTではなくそのまま記述して実行したらどうなりますか？
個人的にはIN,NOT INで複数の項目を使用する事自体間違いだと思います。SQL的に構文エラーにならないとしても、今回のように混乱する元、バグの元となりますからもっと単純にした方が後々の為になるかと･･･
そういう意味では、データベースのテーブル設計自体に問題があるようにも思えます。
今更設計に問題云々と言ってもどうする事も出来ないと思いますから、IN句で指定するSELECT文をVIEWにしてみては如何ですか？もっと簡略化できると思いますよ。

具体的にどうすれば良いか･･･そこがデータベースエンジニアの腕の見せ所です(笑)
どのような意味のテーブルで、ナニをしたいのかがわからないので無関係の第三者には発想を転換する為のヒントと切っ掛けを与えるくらいしか出来ません。

お礼

ありがとうございます。
oracleではIN句に複数カラムを指定することができます。
SQLServerもできると思いますが。
それぞれを単独で実行したら結果が出力されませんでした。
もしかしたら、条件に合致しないから複数でもでないのかしれません。

補足

SQLで「NOT IN句」でテーブルの値の比較をやっているのですが、と書きましたが、
実際は、IDが同じグループで、TNNOが違う行を取得したいのです。
例えば、
ID=Aには、TNNOがTN01とPC01があるのでAとしては
同じ全て同じではないのでその結果を取得していのです。
結果が該当の1行でもすべての行でもかまいません