お気に入りリストのテーブルをSQLで作成する際、取得元テーブルを条件分岐させたい

Question

###実現したいこと
action_tableというテーブルにfavoriteというアクションが記録されています。
サイト閲覧者をuser_id=1と仮定し、彼のfavoriteリストを作りたいです。

###発生している問題
favoriteリストは大体できたのですが、

【問題➀】follow_dateとfavorite_dateがnullとなってしまうこと。
【問題➁】「クジラの生活」の方だけfavorite_tagsが取得できないこと。

が問題となっています。

###該当のテーブル
長くてすみませんが、現在値として仮定したCREATE TABLEを掲載させて頂きます。

テーブルは6つですが、大きく次の3つに分類されます。

分類	テーブル名
『自社ユーザーが実行したアクション』	action_table
『自社ユーザー以外のコンテンツ』	content_tableとcontent_sub_table
『自社ユーザー』	own_user_tableとown_user_sub_table

これらからaction_tableのfavoriteリストを作るわけですが、
favoriteの対象がtarget_type='user'かつtarget_studio='own'のときには『自社ユーザー』のテーブルを参照し、それ以外は『自社ユーザー以外のコンテンツ』を参照する予定です。

以下CREATE TABLEです。

# action_table
# 自社ユーザーが実行したアクション
CREATE TABLE action_table
    (`action_id` int, `action_name` text, `action_date` text, `actor_id` int, `target_id` int, `target_type` text, `target_studio` text, `favorite_tags` text)
;
INSERT INTO action_table
    (`action_id`, `action_name`, `action_date`, `actor_id`, `target_id`, `target_type`, `target_studio`, `favorite_tags`)
VALUES
    (1, 'follow', '2020-01-01 01:01:01', 1, 3, 'user', 'own', ''),
    (2, 'favorite', '2020-02-02 01:02:02', 1, 4, 'documentaly', 'nhk', '"["ドキュメンタリー","クジラ"]"'),
    (3, 'favorite', '2020-03-03 03:03:03', 1, 3, 'user', 'own', '"["田中兄弟"]"'),
    (4, 'block', '2020-04-04 04:04:04', 4, 1, 'user', 'own', ''),
    (5, 'favorite', '2020-05-05 05:05:05', 1, 4, 'user', 'own', ''),
    (6, 'follow', '2020-06-06 06:06:06', 1, 2, 'user', 'own', '')
;

# content_table
# 自社ユーザー（own_user）以外のコンテンツの基本情報
CREATE TABLE content_table
    (`content_id` int, `content_type` text, `content_text` text)
;  
INSERT INTO content_table
    (`content_id`, `content_type`, `content_text`)
VALUES
    (1, 'comment', 'こんにちは'),
    (2, 'review', 'まぁまぁ'),
    (3, 'user', '山田花子です'),
    (4, 'documentaly', 'クジラの生活'),
    (5, 'news', 'ワールドビジネスニュース')
;

# content_sub_table
# 自社ユーザー（own_user）以外のコンテンツの補足情報
CREATE TABLE content_sub_table
    (`sub_id` int, `content_id` int, `sub_key` text, `sub_val` text)
;
INSERT INTO content_sub_table
    (`sub_id`, `content_id`, `sub_key`, `sub_val`)
VALUES
    (1, 1, 'studio_name', 'own'),
    (2, 1, 'content_url', 'own/comment/1'),
    (3, 2, 'studio_name', 'nhk'),
    (4, 2, 'content_url', 'nhk/review/2'),
    (5, 2, 'review_score', '5'),
    (6, 3, 'studio_name', 'fuji'),
    (7, 3, 'content_url', 'fuji/user/hanako'),
    (8, 3, 'unique_name', 'hanako'),
    (9, 3, 'nick_name', '山田花子'),
    (10, 4, 'studio_name', 'nhk'),
    (11, 4, 'content_url', 'nhk/documentaly/クジラの生活'),
    (12, 4, 'airtime', '06:00'),
    (13, 5, 'studio_name', 'asahi'),
    (14, 5, 'content_url', 'asahi/news/ワールドビジネスニュース'),
    (15, 5, 'airtime', '22:00')   
;

# own_user_table
# 自社ユーザーの基本情報
CREATE TABLE own_user_table
    (`user_id` int, `unique_name` text, `nick_name` text)
;  
INSERT INTO own_user_table
    (`user_id`, `unique_name`, `nick_name`)
VALUES
    (1, 'ichiro', '田中一郎'),
    (2, 'jiro', '田中二郎'),
    (3, 'saburo', '田中三郎'),
    (4, 'shiro', '田中四郎')
;

# own_user_sub_table
# 自社ユーザーの補足情報
CREATE TABLE own_user_sub_table
    (`sub_id` int, `user_id` int, `sub_key` text, `sub_val` text)
;
INSERT INTO own_user_sub_table
    (`sub_id`, `user_id`, `sub_key`, `sub_val`)
VALUES
    (1, 1, 'studio_name', 'own'),
    (2, 1, 'content_url', 'own/user/ichiro'),
    (3, 2, 'studio_name', 'own'),
    (4, 2, 'content_url', 'own/user/jiro'),
    (5, 3, 'studio_name', 'own'),
    (6, 3, 'content_url', 'own/user/saburo'),  
    (7, 4, 'studio_name', 'own'),
    (8, 4, 'content_url', 'own/user/shiro')    
;

###試したこと
この質問自体を大きく編集してしまいましたが、いろいろ試した経緯はぐちゃぐちゃとしているので、次のコードまでたどり着いたという現状だけを掲載させて頂きます。

実行サンプル

この実行サンプルの結果が下図で、緑が【問題➀】、赤が【問題➁】です。

# user_id=1のfavoriteリストを取得するのが目標
/*
発生している問題
【問題➀】`follow_date`と`favorite_date`が`null`となってしまうこと。
【問題➁】「クジラの生活」の方だけ`favorite_tags`が取得できないこと。
*/
select
  # 自社ユーザーかそうでないかで取得先テーブルを分岐
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.user_id
      else null
  end as user_id,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then null
      else content.content_id
  end as content_id,  
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.nick_name
      else content.content_text
  end as content_text,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.unique_name
      else null
  end as content_text2,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then 'user'
      else content_type
  end as content_type,   
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user_content_url.sub_val
      else content_content_url.sub_val
  end as content_url, 
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user_studio_name.sub_val
      else content_studio_name.sub_val
  end as studio_name,  
  
  # アクション情報を取得
  his_favorite.follow_date,
  his_favorite.favorite_date,
  action.favorite_tags
  
from (
        select favorite.target_id
             , max(
                  case when action.action_name='follow' then action.action_date end
               ) as follow_date
             , max(
                  case when action.action_name='favorite' then action.action_date end
               ) as favorite_date
        from  action_table favorite
              left join action_table action
              on  favorite.actor_id=action.target_id 
              and action.actor_id=1 
        where favorite.actor_id=1 and favorite.action_name='favorite'
          and favorite.actor_id not in(
                select case when target_id=1 then actor_id else target_id end
                from action_table
                where target_type='user' and action_name='block' and 1 in (target_id, actor_id) 
              )
        group by favorite.target_id   
      ) his_favorite

      # アクション情報
      left join action_table action
      on  his_favorite.target_id=action.action_id

      # 自社ユーザー以外の情報
      left join content_table content
      on  his_favorite.target_id=content.content_id
      left join content_sub_table content_studio_name
      on  his_favorite.target_id=content_studio_name.content_id and content_studio_name.sub_key='studio_name'
      left join content_sub_table content_content_url
      on  his_favorite.target_id=content_content_url.content_id and content_content_url.sub_key='content_url'

      # 自社ユーザーの情報
      left join own_user_table own_user
      on  his_favorite.target_id=own_user.user_id      
      left join own_user_sub_table own_user_studio_name
      on  his_favorite.target_id=own_user_studio_name.user_id and own_user_studio_name.sub_key='studio_name'
      left join own_user_sub_table own_user_content_url
      on  his_favorite.target_id=own_user_content_url.user_id and own_user_content_url.sub_key='content_url'
      left join own_user_sub_table own_user_content_text
      on  his_favorite.target_id=own_user_content_text.user_id and own_user_content_text.sub_key='content_text'

###補足情報（FW/ツールのバージョンなど）

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

SQL初心者がネットの見本などを参考に組み合わせたコードにつき、意味不明な部分などがあるかもしれませんがご容赦ください。

宜しくお願い致します。

Comments

[Orlofsky]

CREATE TABLEやINSERTの提示を求めても無視する質問者が多い中、最初からCREATE TABLEやINSERTをきちんと提示できるのは良いことです。
ですが、質問内容があり過ぎて何を質問したいのか読み解く負担が多過ぎです。
質問したい内容に絞ったテーブルや列で提示されては？

[ikatako]

まったく仰る通りかと思います。お恥ずかしい限りです。

あれからずっと取り組んでいるのですがなんとか条件分岐の方は（たぶん）できまして、残るはaction_tableのデータの問題だけになりました。

改めてその点だけに絞って質問を編集させて頂きますので、少々お時間頂戴できましたら幸いです。いつもご指摘ありがとうございます。

[ikatako]

今しがた質問を編集させて頂きました。お手すきの折にご回答いただけましたら幸甚に存じます。