深さの決まっていない親子関係のデータを上手く取得する方法が知りたい

Question

前提

まず例として、企業の合併情報を管理している下記のようなテーブルがあるとします。

現時点で一番親の会社はA社ですが、このA社の情報を取得する際に紐づくすべての子会社の情報も取ってきたいです。
（A社の直属の子会社であるB社、C社だけでなくB社の子会社、その下のE社の子会社も取りたい）

上記の子会社情報を階層のない1つのリストするのが最終目標です。

現状

最初はA社の直属とその下（B社の子会社）まで取ってくる仕様でしたので、今の実装では
合併明細テーブルにAAAで検索をかけて取得したリストをループ→その中で更に合併明細にBBB、CCCで検索かけてリストに追加
という形で一度だけループをしています。

知りたいこと

親子関係の階層がどれくらいの深さになるかが分からないためループで取得するのは非現実的かと思っているのですが、
調べてもなかなかいい方法が出てこず困っています。
JavaでもSQLでも構いませんので何かアドバイスをいただけたら幸いです。
言葉足らずな部分ありましたら補足しますのでコメントください。よろしくお願い致します。

解決後追記

以下、ベストアンサーを参考に組んだSQLです。

with temp (親会社ID, 子会社ID, lvl) as (
 select 親会社ID, 子会社ID, 1 from 合併明細テーブル
 where 親会社ID = 'AAA'
 union all
 select G.親会社ID, G.子会社ID, T.lvl+1 from 合併明細テーブル G, temp T
 where G.親会社ID = T.子会社ID
) search breadth first by 子会社ID set order1
select 親会社ID, 子会社ID, lvl from temp;

取得結果

ご協力いただいた皆様ありがとうございました。

Comments

[KOZ6.0]

階層問い合わせ
https://docs.oracle.com/cd/E57425_01/121/SQLRF/queries003.htm

[onakahetta]

ありがとうございます。階層問い合わせについて知らなかったので勉強します。

[68user]

ああ、Oracle にそういう機能があるんですね。ではこっちの方がいいですね。失礼しました。

Answer 1

合併明細テーブルはツリー構造なので、再帰を使用すれば取得できると思います。
図でイメージするOracle DatabaseのSQL全集 第7回 再帰with句
津島博士のパフォーマンス講座　第31回　再帰的問合せについて

Comments

[onakahetta]

階層問い合わせとはまた別に再起問い合わせというものがあるんですね。
記載いただいた記事を参考にして、まさに欲しかったデータを取得することができました！
本当にありがとうございます。

Answer 2

一応 java も。普通になぞっているだけですが。

import java.util.*;

public class Main {
    public static void main(String[] args) throws Exception {
        List<Entry> 合併明細 = new ArrayList<>();
        合併明細.add(new Entry("AAA", "BBB"));
        合併明細.add(new Entry("AAA", "CCC"));
        合併明細.add(new Entry("BBB", "DDD"));
        合併明細.add(new Entry("BBB", "EEE"));
        合併明細.add(new Entry("EEE", "FFF"));
        合併明細.add(new Entry("EEE", "GGG"));

        List<String> 結果 = new ArrayList<>();
        結果.add("AAA");
        for(int i=0; i<結果.size(); i++) {
            for(Entry entry : 合併明細) {
                if(entry.親会社ID.equals(結果.get(i)) && !結果.contains(entry.子会社ID)) {
                    結果.add(entry.子会社ID);
                } 
            }
        }
        System.out.println("" + 結果);
    }
    private static class Entry {
        final String 親会社ID;
        final String 子会社ID;
        Entry(String 親会社ID, String 子会社ID) {
            this.親会社ID = 親会社ID;
            this.子会社ID = 子会社ID;
        }
    } 
}

[AAA, BBB, CCC, DDD, EEE, FFF, GGG]

(paiza.io で作成・実行)

Answer 3

階層数が一定でないツリー構造を一発の SQL で取るのは難しいです。
ご参考:
https://www.wantedly.com/companies/autoro/post_articles/299434
https://bbh.bz/2020/07/18/tree-structure-in-rdb/

そもそもデータ構造がよくない気がします。子会社をフラットにもったテーブルを作り、会社情報変動時にレコード再作成してはどうでしょうか。

本題と関係ないですが、下記が気になりました。
・「合併」したのに「親会社」「子会社」という関係性が出てくるのがしっくりこない
・あるタイミングの合併において、どの会社と合併したのかという情報が見えなくなっている。そもそも「合併ID」があるべきでは。

Comments

[onakahetta]

ありがとうございます。
合併したら新しい会社ができて合併元の会社と親子関係になる、という動きをするので親会社・子会社という表現を使いました。あくまで例えなので、一般的な企業間の合併とは少し感じが違うかもしれないです。かえって分かりづらくしてしまいすみません。
データ構造は他機能との兼ね合いもあり今から変えるのが難しく...ビューを使用すれば実現できるでしょうか？

[68user]

実際のデータがツリー構造であるべき or 現状そうなっているのは仕方がないというならそれはそれでよくて、であるなら 1回のSQLでは取れないので
　・SELECT 時に頑張る (階層分SELECTを続けるとか、SELF JOIN 5回おこなって 5階層までは取得できるがまだ深いデータがあるなら再度 SELECT するなど)
　・SELECT 時に頑張らなくてよいよう INSERT/UPDATE 時に親子関係情報をふまえたキャッシュテーブル的なものを作っておく (全件メモリやRedis上に持っておくとか、最上位会社IDと子会社ID のマッピングテーブルを作っておくとか)
のどちらかではないでしょうか。

どちらで頑張るかは SELECT と、INSERT/UPDATE の頻度やデータ件数によるのではと思います。

[onakahetta]

具体的に方法を示していただきありがとうございます。とても参考になります。
回答に記載くださった記事も読みましたがそもそものデータ構造がこういうデータの取得に向いていないかもしれない、という発想は自分にはなかったので勉強になりました。
コメントにいただいていた階層問い合わせも含め、検討しようと思います。