【SQLレシピ】階層を持つテーブルで指定したレコードと属するレコードをSELECTする

SQLで階層を持つテーブルで指定したレコードと属するレコードをSELECTしたい！
「北海道・東北」を指定したら属する都道府県名が選択できる的な！

概要

この記事では、SQLで階層を持つテーブルで指定したレコードと属するレコードをSELECTする手順を掲載する。

仕様書

環境

• SQLite 3.35.5
• SQL Server 15.0.2000

手順書

下表のようを「国～方面～都道府県名」を管理してるテーブルbaseを例にする。

登録してるデータは下記のとおり。

今回は、「北海道・東北」のidを指定して、指定したidのレコードとそのidに属するレコードを選択するクエリを紹介する。

SQLiteとSQL Server共通。

WITH hierarchy AS (
  SELECT
    id,
    parent_id,
    name
  FROM
    base
  WHERE
    id = 2
  UNION ALL
  SELECT
    t.id,
    t.parent_id,
    t.name
  FROM
    base t
    INNER JOIN hierarchy h ON h.id = t.parent_id
)
SELECT * FROM hierarchy;

クエリを実行すると下記のように出力される。

まとめ（感想文）

組織で部に属するグループを選択したい場合とかに使えるかもね！