SQL文が浮かびません。

とあるOracleの問題集の問題ですが、

同じ人物が複数のMEMBER_IDを持っていないかチェックしたい（FIRST_NAME列とLAST_NAME列が同じで、MEMBER_ID列が異なる）。
1つのSQL文で、重複する名前および対応するMEMBER_IDをすべて表示するために必要な方法は？

という問題で、
正解（2つ選択）は、「自己結合」と「副問合せ」でした。

「自己結合」のSQL文は浮かんだのですが、「副問合せ」のSQL文が浮かびません。
誰か美しいSQL文を教えてください。

No.2 ベストアンサー 回答者： yambejp 回答日時： 2017/07/14 16:20

なるほどその他の要素が必要なのですね。

では結果をもとのテーブルのwhere句で参照してみてはいかがでしょうか？

SELECT * FROM tbl
where (FIRST_NAME,LAST_NAME) in (
select FIRST_NAME,LAST_NAME from tbl
having count(DISTINCT MEMBER_ID)>1
group by FIRST_NAME,LAST_NAME
)

この回答へのお礼

再度のご回答、ありがとうございます。
なるほど、これだと確かに「副問合せ」の回答例になりますね。

回答の選択肢で「GROUP BY句とHAVING句」は間違いとされていたので、副問合せの中で使うという考えには至りませんでした。
「Bは間違いだけど、Bを含むAは正解」というのは問題として美しくないなあ。
でも「GROUP BY句とHAVING句」を副問合せの中で使うのが正解ですかね。

お礼日時：2017/07/14 17:16

No.4 回答者： yambejp 回答日時： 2017/07/14 17:40

あとはこうとか？

select * from tbl as t1
where exists (
SELECT MEMBER_ID
FROM tbl as t2
WHERE not t1.MEMBER_ID=t2.MEMBER_ID
and t1.FIRST_NAME,=t2.FIRST_NAME,
and t1.LAST_NAME=t2.LAST_NAME;
);

No.3 回答者： yambejp 回答日時： 2017/07/14 17:32

もしくはこんな感じでも

select distinct t1.MEMBER_ID
,t1.FIRST_NAME
,t1.LAST_NAME
from tbl as t1
inner join tbl as t2
on not t1.MEMBER_ID=t2.MEMBER_ID
and t1.FIRST_NAME,=t2.FIRST_NAME,
and t1.LAST_NAME=t2.LAST_NAME;

No.1 回答者： yambejp 回答日時： 2017/07/14 15:41

こんな感じじゃないでしょうか？

select FIRST_NAME,LAST_NAME from tbl
having count(*)>1
group by FIRST_NAME,LAST_NAME

この回答へのお礼

MEMBER_IDも表示したいみたいです。
「GROUP BY句とHAVING句を使う方法だと、同時にMEMBER_IDを表示できません。」という解説が付いてました。

お礼日時：2017/07/14 16:03