質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.49%

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

解決済

回答 4

投稿

  • 評価
  • クリップ 1
  • VIEW 273

tomona

score 32

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

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

以下のテーブル構成となっています。
自社の勘定科目のテーブル: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となるようなレコードを抽出したいと考えています。
アドバイス頂けると幸いです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/09/23 15:41

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

    キャンセル

  • tomona

    2019/09/23 15:52

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

    キャンセル

  • sazi

    2019/09/27 15:09

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

    キャンセル

  • tomona

    2019/09/30 17:42

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

    キャンセル

回答 4

checkベストアンサー

0

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の中でやると一致しないのもとれますよ

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/09/30 17:46

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

    キャンセル

0

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>

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/09/24 00:55

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

    キャンセル

  • 2019/09/24 01:25

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

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • MySQLに関する質問
  • 主テーブルの全データを出しつつ結合したテーブルに条件を付ける方法(結合テーブルの条件に合致しない場合はNULLとする)