108 views

1. やりたいこと

以下のようなテーブル構成のシステムを作っているとする。

このとき、指定したクラブ且つ、指定した学校に所属している人の名前を検索したい。

すなわち、
membershipテーブルから指定したクラブ(club)に所属している人(person)を検索し、
affiliationテーブルから指定した学校(school)に所属している人(person)を検索し、
その人の名前を表示したい。

つまり、三つのテーブルの情報を結合してデータ検索を行いたい。
各テーブルの仕様は以下の通り。

・personテーブルは、人の識別番号(pid)と人の名前(pname)を持つ。

・clubテーブルは、クラブの識別番号(cid)とクラブ名(cname)を持つ。

・schoolテーブルは、学校の識別番号(sid)と学校名(sname)を持つ。

・membershipテーブルは、クラブの識別番号と人の識別番号だけを持つ。

・affiliationテーブルは、学校の識別番号と人の識別番号だけを持つ。

よって、複数のテーブルを横断して情報収集する必要がある。
これを 1回のクエリ発行で実現したい。

2. やってみる

(1) A中学校の野球部に所属している人を検索する。

pid=1, 6の二人がこの条件にマッチする。

以下のSQLを実行する。

SELECT SQ1.pid, SQ1.pname
FROM (
  SELECT person.pid, person.pname
  FROM membership
  JOIN person ON membership.cid='1' AND membership.pid=person.pid
) AS SQ1
JOIN (
 SELECT person.pid
 FROM affiliation
 JOIN person ON affiliation.sid='1' AND affiliation.pid=person.pid
) AS SQ2
ON SQ1.pid = SQ2.pid;

OKだ。意図した通りの検索結果が得られた。

今回も上記のSQL文を ChatGPTに解説していただこう。
本当に便利な大賢者様だ。

SELECT person.pid, person.pname
FROM membership
JOIN person ON membership.cid='1' AND membership.pid=person.pid

SELECT person.pid
FROM affiliation
JOIN person ON affiliation.sid='1' AND affiliation.pid=person.pid