【自己結合】SQLで1対多のテーブルから特定のデータを抜き出す

管理者に対して複数のユーザーが紐づくなど1:多のテーブルから特定のユーザーを抜き出したいケースなどあると思います。
そういう時は、同じテーブル内で自己結合を使うと特定のデータを抜き出すことができて便利です。
今回は自己結合を使った、1:多のテーブルからデータを抽出する方法をご紹介します。
SQLの自己結合は、同じテーブル内の異なるレコードを関連付ける方法です。自己結合を使用することで、テーブルのデータをより詳細に分析できます。

サンプルデータ

例のような1つのadmin_idに対して複数のuser_idが紐づくケースで説明します。

admin_id	user_id	created_at
1234	abcde	2023-01-03
1234	fghijh	2023-02-28
3456	s1wert	2023-03-02
3456	dgruar	2023-02-21

今回のケースのようなテーブルに対し、他のテーブルのadmin_idをkeyにして紐づくuserの状況を確認するとします。
そのまま結合するとuser_idが複数紐付けられてしまうので1つだけに絞りたい時などに今回紹介する自己結合が便利です。

抽出方法

ポイントとしては、抽出したい今回のテーブルに対し条件を絞った今回のテーブルをJOINするだけになります。
今回はcreated_atが最新のuserだけを抜き出すようにします。

SELECT t1.admin_id, t1.user_id, t1.created_at
FROM table_name AS t1
INNER JOIN (
    SELECT admin_id, MAX(created_at) AS max_date
    FROM table_name
    GROUP BY admin_id
) AS t2 ON t1.admin_id = t2.admin_id AND t1.created_at = t2.max_date;

得られる結果はこちらになります。

admin_id	user_id	updated_at
1234	fghijh	@February 28, 2023
3456	s1wert	@March 2, 2023

自己結合を使うことで、1:多のテーブルから任意のデータを抽出することができました！
上記のSQLはMAXを使いましたが、比較してやる方法もあります。

SELECT t1.admin_id, t1.user_id, t1.updated_at
FROM table_name t1
LEFT JOIN table_name t2
ON t1.admin_id = t2.admin_id AND t1.updated_at < t2.updated_at
WHERE t2.admin_id IS NULL;

ぜひ使ってみてください〜！