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

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

ただいまの
回答率

87.37%

「LEFT OUTER JOIN」で、取得できないケースを解決したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 798

score 244

前提・実現したいこと

「jp_contents_table」から「comment」を取得しています。

この「attach_id」のカラムの値が0でないとき、
コメントへの添付データとして「attached_xxx」というカラムを連結したいです。

しかし添付データの取得先を分岐させるにあたって、問題が生じています。

発生している問題

図でご説明します。

まず「comment」である【content_id=11,7,3,1】が取得されています。

このうち「attach_id」のカラムの値が0でないのは【content_id=11,7,3】で、
この3つが添付データを持つということです。

なのでこの3つには「attached_xxx」のカラムに値を入れたいと思っています。

そして【content_id=7】は「attached_xxx」のカラムに値が入っていて問題ありませんが、(図の青枠)

なぜか【content_id=11,3】は「attached_xxx」のカラムの値がnullとなってしまいます。(図の赤枠)

イメージ説明

問題のソースコード

発生している問題を起こすソースコードはこちらです。
(文字数制限の都合上、リンクだけとさせて頂きました。)

目的の結果

こちらが目的の結果です。図の赤枠でnullとなっている部分は次のように埋めたいです。
このように埋まるロジックは次に記します。

CREATE TABLE result
    (`content_id` int, `author_id` int, `content_date` text, `parent_thread_id` int, `parent_content_id` int, `content_type` text, `content_text` text, `attach_id` int, `attach_type` text, `attach_country_code` text, `attached_id` varchar(6), `attached_type` varchar(6), `attached_country_code` varchar(6), `attached_nick_name` varchar(6), `attached_content_text` varchar(13), `attached_follow_date` varchar(6), `attached_favorite_date` varchar(19))
;
INSERT INTO result
    (`content_id`, `author_id`, `content_date`, `parent_thread_id`, `parent_content_id`, `content_type`, `content_text`, `attach_id`, `attach_type`, `attach_country_code`, `attached_id`, `attached_type`, `attached_country_code`, `attached_nick_name`, `attached_content_text`, `attached_follow_date`, `attached_favorite_date`)
VALUES
    (11, 2, '2020-11-11 11:00:00', 8,  0, 'comment', '米国の映画スレを添付します',       9, 'thread', 'jp', 9,   'thread', 'jp', null,       '米国の映画について語るスレ', null, null),
    (7, 1,  '2020-07-07 07:00:00', 10, 0, 'comment', '日本の田中三郎さんを添付します。', 3, 'user',   'jp', 3,   'user',   'jp', '田中三郎', null,                         null, '2020-03-03 03:00:00'),
    (3, 4,  '2020-03-03 03:00:00', 8,  0, 'comment', '韓国の지안さんを添付します。',     3, 'user',   'ko', 3,   'user',   'ko', null,       '지안',                       null, null),
    (1, 3,  '2020-01-01 01:00:00', 9,  0, 'comment', '米国のお勧め映画は何ですか?',     0, null,     null, null, null,    null, null,       null,                         null, null)
;

取得のロジック

添付データの取得先は、
『jpのuser』ならば「jp_users_table」のテーブルにあり、
『jpだけどuser以外』なら「jp_contents_table」のテーブルにあり、
『jp以外』なら「xx_contents_table」のテーブルにある
という構造になっています。

(『jpのuser』とは、『attach_country_code='jp' かつ attach_type='user'』ということです。)

レコード 「attach_id」 分類 添付データの取得先
【content_id=11】 9 『jpだけどuser以外』 jp_contents_table
【content_id=7】 3 『jpのuser』 jp_users_table
【content_id=3】 3 『jp以外』 xx_contents_table

そのため取得のロジックは、

【content_id=11】は「jp_contents_table」から「content_id=9」を添付データとして扱いますので、目的の結果では「attached_content_text」のカラムに米国の映画について語るスレが入っています。

【content_id=7】は「jp_users_table」から「user_id=3」を添付データとして扱いますので、目的の結果では「attached_nick_name」のカラムに田中三郎が入っています。

【content_id=3】は「xx_contents_table」から「content_id=3」を添付データとして扱いますので、目的の結果では「attached_content_text」のカラムに지안が入っています。

試したこと

まず「attached_id」のカラムを定義している、上記SQL fiddleの32~37行目のas attached_idに注目しました。

これが取得できていないから、【content_id=11,3】のレコードの「attached_id」のカラムがnullとなっているのだと思います。

そこでこの32~37行目を下記のように変更してみました。
'テスト値' as attached_id

しかし【content_id=11,3】のレコードの「attached_id」のカラムはnullのままなのです。

「attached_id」というカラムを定義している部分はここしかないので、nullでなくテスト値が入ると思ったのですが、なぜnullのままなのでしょうか。ここを変更してもダメとなると、何を変更したらいいのかわからなくなってしまいました。

補足情報(FW/ツールのバージョンなど)

phpMyAdmin(4.8.5)で、MySQL(10.0.33-MariaDB)を使っています。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • ikatako

    2020/02/14 05:49 編集

    yambejpさん、遅くなりました。ただいま取得のロジックを追加させて頂きました。

    かなり入り組んだ長い文章になってしまいまして、また不備などあるかもしれませんが、その折はまたご指摘いただけましたら幸いです。

    尚、create table+insertは文字数の都合上、SQL fiddleの方へ掲載させて頂きました。

    キャンセル

  • ikatako

    2020/02/14 06:54 編集

    amuraさん、なるほど。ありがとうございます。

    「何故9がないのか」ですが、「タイムラインに流すものへの添付データに対して、必ず何かアクションをしているとは限らないから(xx_contents_tableのattach_idは、必ずjp_actions_tableにあるわけではないから)」ということになります。

    キャンセル

  • ikatako

    2020/02/14 11:36

    yambejpさん、失礼いたしました。目的の結果を失念しておりましたので、改めて質問に掲載させて頂きました。もしお時間ございましたら、どうぞ宜しくお願い致します。

    キャンセル

回答 1

checkベストアンサー

0

「xx_contents_table」の「content_id=9」を添付データとして扱っており

の部分はxx_contents_tableに9が存在していない理由が分かりませんでした

同じく【content_id=2】のレコードは、

SQLを少し直してみました、行数が多いので下記に記入しました
問題のgroup byはcase結果で行いたく数字としました。(修正しました)

# 最終的な取得
# 最終的な取得
select *
from
  (
    select *
    from jp_contents_table
    where timelines regexp 'thread_3|author_2|tag_2' and content_type='comment' or content_type='reply'
  ) as timeline_list

  left outer join
  (
     # 添付データの取得
     select
          jp_actions.target_id as attached_content_id
        , case when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
               then jp_actions.target_type
               else jp_contents.attach_type end as attached_content_type
        , case when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
               then jp_actions.target_country_code
               else jp_contents.attach_country_code end as attached_country_code
        , case
           when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
           then jp_users.nick_name
           else null
        end as attached_nick_name
        ,case
           when jp_actions.target_country_code='jp' then
               case when jp_actions.target_type='user' then null
               else jp_contents.content_text end
           else xx_contents.content_text
        end as attached_content_text
        # 添付データへのアクション日時
        , max( case
           when jp_actions.action_name='follow'
           then jp_actions.action_date
        end ) as attached_follow_date
        , max( case
           when jp_actions.action_name='favorite'
           then jp_actions.action_date
        end ) as attached_favorite_date

     from  jp_actions_table jp_actions

     # 添付データのテーブルをjoin
     left join jp_users_table jp_users
     on  jp_actions.target_id=jp_users.user_id
     left join (
          select t1.content_id, t1.author_id, t1.content_date, t1.parent_thread_id,
          t1.parent_content_id, t1.content_type,
          case when t1.attach_type='user' and t1.attach_country_code='jp'
               then t1.content_text else t2.content_text end content_text,
          t1.tag_ids,t1.timelines, t1.attach_id, t1.attach_type, t1.attach_country_code
          from jp_contents_table t1 left join jp_contents_table t2
          on t1.attach_id=t2.content_id
     ) jp_contents
     on  jp_actions.target_id=jp_contents.attach_id
     left join xx_contents_table xx_contents
     on  jp_actions.target_id=xx_contents.content_id

     # 彼がactor_idであるもの
     where jp_actions.actor_id=1
     # favorite_dateとfollow_dateが欲しい
     and jp_actions.action_name in ('favorite', 'follow')
     and exists(
          select 0 from jp_actions_table
          where target_id=jp_actions.target_id and action_name in ('favorite','follow')
     )
     # 彼とblock関係にあるjpのuserを除外
     and not exists(
          select 0 from jp_actions_table
          where target_type='user' and target_country_code='jp' and action_name='block'
          and target_id=jp_actions.target_id and jp_actions.target_type='user'
          and actor_id=1
     )

     #group by jp_actions.target_id, jp_actions.target_type, jp_actions.target_country_code
     group by attached_content_id,attached_content_type,attached_country_code

  ) as action_list

on timeline_list.attach_id=action_list.attached_content_id
and timeline_list.attach_type=action_list.attached_content_type
and timeline_list.attach_country_code=action_list.attached_country_code

order by content_date desc
;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2020/02/15 07:07

    一晩かかってしまいましたが、頂いたものを参考に、こちら↓までたどり着きました。
    http://sqlfiddle.com/#!9/3d40de/116
    こちらですとselectの回数が少なくて済むかと思っているのすが、いかが思われますか?
    よろしければamura様のご高察を賜りたく存じます。

    キャンセル

  • 2020/02/15 09:03

    ご苦労さまです。私はゴールを理解せずに結果からの推測にて作成していますので、今回正しく結果が求められれば良いと考えます。
    timeline_list側を変えた事は良いと思いました。

    キャンセル

  • 2020/02/15 09:46

    何をいつjoinするか順序が混乱していたのでおかげで助かりましたし、今回も大変勉強になりました。どうもありがとうございます。

    キャンセル

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

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る