主テーブルの全データを出しつつ結合したテーブルに条件を付ける方法（結合テーブルの条件に合致しない場合はNULLとする）

Question

以下のケースを想定して抽出方法をご教示頂けないでしょうか。

会計システムを作っています。
２種類の勘定科目マスター（自社の勘定科目、グループ会社用の勘定科目）があり、その変換表を作っています。

以下のテーブル構成となっています。
自社の勘定科目のテーブル：company_m_accounts
グループの勘定科目のテーブル：groupm_accounts
対応テーブル：company_t_map_accounts

自社の勘定科目に対応しているグループ会社の勘定科目が対応しているかグループ毎に確認したく、company_m_accountsの全データとそれに紐づくデータを取得する方法をご教示いただけますと幸いです。
以下のテストケースをご覧頂くとわかりやすいと思います。

-- 勘定科目マスターの定義テーブルの作成
DROP TABLE IF EXISTS company_m_accounts;
		
CREATE TABLE company_m_accounts (
  company_id CHAR(3) NOT NULL,
  account_id VARCHAR(255) NOT NULL,
  PRIMARY KEY (company_id,account_id)
) DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS group_m_accounts;
-- 勘定科目マスター(グループ別)
CREATE TABLE group_m_accounts (
  group_id CHAR(3) NOT NULL,
  group_account_id VARCHAR(255) NOT NULL,
  PRIMARY KEY (group_id,group_account_id)
) DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS company_t_map_accounts;
-- 勘定科目マッピングテーブル
CREATE TABLE company_t_map_accounts (
  company_id CHAR(3) NOT NULL,
  account_id VARCHAR(255) NOT NULL,
  group_id CHAR(43) NOT NULL,
  group_account_id VARCHAR(255) NOT NULL,
  PRIMARY KEY (company_id,account_id,group_id,group_account_id)
)DEFAULT CHARSET=utf8;

-- 勘定科目レコードの投入
INSERT INTO company_m_accounts VALUES('com','1000001');
INSERT INTO company_m_accounts VALUES('com','1000002');
INSERT INTO company_m_accounts VALUES('com','1000003');
INSERT INTO company_m_accounts VALUES('com','1100001');
INSERT INTO company_m_accounts VALUES('com','1110000');


-- グループ勘定科目レコードの投入
INSERT INTO group_m_accounts VALUES('grp','1000001');
INSERT INTO group_m_accounts VALUES('grp','1000002');
INSERT INTO group_m_accounts VALUES('grp','1000003');
INSERT INTO group_m_accounts VALUES('grp','1100001');
INSERT INTO group_m_accounts VALUES('grp','1110000');

作成したSQL

SELECT A.account_id, C.group_account_id
FROM company_m_accounts AS A 
LEFT OUTER JOIN company_t_map_accounts AS B
ON A.company_id = B.company_id AND A.account_id = B.group_account_id
LEFT OUTER JOIN group_m_accounts AS C
ON B.group_id = C.group_id AND A.account_id = B.group_account_id
WHERE A.company_id = 'com' AND B.group_id= 'grp';

想定される結果

account_id	group_account_id
1000001	NULL
1000002	NULL
1000003	NULL
1000004	NULL
1000005	NULL

上記のSelect文の場合、group_idで絞るためレコードがすべてNULLとなります。しかし、company_m_accountsのレコードはすべて抽出した上でgroup_account_idがNULLとなるようなレコードを抽出したいと考えています。
アドバイス頂けると幸いです。

Comments

[Orlofsky]

company_t_map_accounts の INSERT が1行もないけど良いの？

[tomona]

大丈夫です。
company_t_map_accountsは空欄のため結果としてgroup_idがNULLのレコードを取得したいためです。

[sazi]

>変換表を作っています。
company_t_map_accountsという事ですよね？
だとすると変換のルールというのは、account_id とgroup_account_idが等しい場合ですか？
それだと、company_t_map_accountsを作る必要性を感じないのだけれど。

[tomona]

>変換のルールというのは、account_id とgroup_account_idが等しい場合ですか？
必ずしも等しいとは限りません。

Answer 1

変換表を作りたいとのことなので、こういう感じではないでしょうか。

SELECT
    D.group_id
,   A.account_id
,   C.group_account_id
FROM
    company_m_accounts  AS  A
    CROSS JOIN
        (
            SELECT
                group_id
            FROM
                group_m_accounts
            GROUP BY
                group_id
        )   AS  D
    LEFT OUTER JOIN
        company_t_map_accounts  AS  B
        ON  B.company_id        =   A.company_id
        AND B.account_id        =   A.account_id
    LEFT OUTER JOIN
        group_m_accounts        AS  C
        ON  C.group_id          =   D.group_id
        AND C.group_account_id  =   B.group_account_id
WHERE
    A.company_id                =   'com'
-- AND D.group_id                  =   'grp'

group_id	acount_id	group_account_id
grp	1000001	NULL
grp	1000002	NULL
grp	1000003	NULL
grp	1100001	NULL
grp	1110000	NULL

Answer 2

なにがどうリレーションしているのかわからないですがこういうことですか？

select t1.account_id,t3.group_account_id from company_m_accounts as t1
left join company_t_map_accounts as t2 on t1.company_id = t2.company_id
left join group_m_accounts as t3 on t2.group_account_id = t3.group_account_id

Answer 3

SELECT A.account_id, C.group_account_id
FROM company_m_accounts AS A 
LEFT OUTER JOIN company_t_map_accounts AS B
ON A.company_id = B.company_id AND A.account_id = B.group_account_id
LEFT OUTER JOIN group_m_accounts AS C
ON B.group_id = C.group_id AND B.group_account_id = C.group_account_id
AND B.group_id= 'grp'
WHERE A.company_id = 'com' ;

Cの結合条件がA.account_idとなっていたのがうまくとれない原因の一つです
グループでのしぼりこみはWHEREでやると全体がしぼりこまれてしたうためONの中でやると一致しないのもとれますよ

Comments

[tomona]

まさにこちらです！
ONの中で条件指定が出来るのですね。大変勉強になりました。
ベストアンサーのみならず、皆様ご回答ありがとうございました。

Answer 4

WHEREの最後に

B.group_id= 'grp';

とありますが、company_t_map_accounts にはデータが1件もないので、SELECT されるデータは1件もないかと。

追記
Oracle での実行結果

SQL> SET NULL null
SQL> SELECT
  2      A.account_id
  3    , C.group_account_id
  4  FROM company_m_accounts A  -- ※ Oracle ではここで AS は記述できない
  5  LEFT OUTER JOIN company_t_map_accounts B  -- ※
  6  ON  A.company_id = B.company_id
  7  AND A.account_id = B.group_account_id
  8  LEFT OUTER JOIN group_m_accounts C  -- ※
  9  ON  B.group_id = C.group_id
 10  AND A.account_id = B.group_account_id
 11  WHERE A.company_id = 'com'
 12  AND   B.group_id = 'grp';

レコードが選択されませんでした。

SQL> -- AND   B.group_id = 'grp' を削ると、
SQL> SELECT
  2      A.account_id
  3    , C.group_account_id
  4  FROM company_m_accounts A
  5  LEFT OUTER JOIN company_t_map_accounts B
  6  ON  A.company_id = B.company_id
  7  AND A.account_id = B.group_account_id
  8  LEFT OUTER JOIN group_m_accounts C
  9  ON  B.group_id = C.group_id
 10  AND A.account_id = B.group_account_id
 11  WHERE A.company_id = 'com';

ACCOUNT_ID GROUP_ACCO
---------- ----------
1000001    null
1000002    null
1000003    null
1100001    null
1110000    null

SQL>

Comments

[tomona]

その場合、想定される結果にある表のようにNULLを返して欲しいのですがそういった抽出は不可能ですか？

[Orlofsky]

SQLは実際に実行して動作を確認します。CREATE TABLEもINSERTもあるから実行できる環境を用意して動作確認しては？