SQLで「SELECTの結果」を連結したい

Question

###実現したいこと
「スレッドへのコメントリスト」と「お気に入りリスト」を連結したいです。

コメントへは添付データがありうるので、「コメントへの添付データをお気に入りしているかどうか」も、コメントと一緒に表示したいためです。

それぞれ別々のSELECTはできており、あとは連結だけなので簡単かと思ったのですが躓いてしまいました。

以下とても長い内容になってしまいましたが、ご覧いただけましたら幸いでございます。

###対象のテーブル
後述するすべてのSQL fiddleのテーブルのCREATE TABLEを念のため掲載いたします。(SQL fiddleにあるものと同じなのでご覧いただかなくとも結構です。)

# jp_actions
# アクションのテーブル
CREATE TABLE jp_actions
    (action_id int, action_name text, action_date text, actor_id int, target_id int, target_type text, target_country_code text)
;
INSERT INTO jp_actions
    (action_id, action_name, action_date, actor_id, target_id, target_type, target_country_code)
VALUES
    (1,  'follow',   '2020-01-01 01:00:00', 1, 3, 'user',  'jp'),
    (2,  'favorite', '2020-02-02 02:00:00', 1, 4, 'drama', 'zh'),
    (3,  'favorite', '2020-03-03 03:00:00', 1, 3, 'user',  'jp'),
    (4,  'favorite', '2020-04-04 04:00:00', 2, 5, 'user',  'jp'),
    (5,  'favorite', '2020-05-05 05:00:00', 1, 7, 'user',  'jp'),
    (6,  'follow',   '2020-06-06 06:00:00', 1, 2, 'user',  'jp'),
    (7,  'follow',   '2020-07-07 07:00:00', 1, 7, 'user',  'jp'),
    (8,  'block',    '2020-08-08 08:00:00', 1, 5, 'user',  'jp'),
    (9,  'favorite', '2020-09-09 09:00:00', 1, 5, 'user',  'jp'),
    (10, 'favorite', '2020-10-10 10:00:00', 1, 5, 'movie', 'en'),
    (11, 'favorite', '2020-11-11 11:00:00', 1, 3, 'user',  'en'),
    (12, 'favorite', '2020-12-12 12:00:00', 1, 3, 'thread','jp')
;

# country_contents
# コンテンツのテーブル
CREATE TABLE country_contents
    (content_id int, content_type text, country_code text, content_title text)
;  
INSERT INTO country_contents
    (content_id, content_type, country_code, content_title)
VALUES
    (1, 'user',  'en', 'smith'),
    (2, 'manga', 'zh', '砂輿海之歌'),
    (3, 'user',  'ko', '지안'),
    (4, 'drama', 'en', 'Mr.ROBOT'),
    (5, 'movie', 'en', 'Avator'),
    (6, 'movie', 'ru', 'Хардкор'),
    (7, 'drama', 'zh', '大秦帝国')
;

# jp_threads
# 掲示板のテーブル
CREATE TABLE jp_threads
    (thread_id int, thread_text text, thread_theme text, allowed_user_ids text)
;  
INSERT INTO jp_threads
    (thread_id, thread_text, thread_theme, allowed_user_ids)
VALUES
    (1, 'かわいい猫の画像スレ', '画像のこと', '"[1,2,3]"'),
    (2, '結婚できない女のスレ', '結婚のこと', '"[2,3]"'),
    (3, 'お酒に合う音楽のスレ', '音楽のこと', '"[1,3]"')
;

# jp_voices
# コメントや返信のテーブル
CREATE TABLE jp_voices
    (voice_id int, thread_id int, parent_id int, voice_type text, voice_text text, up int, down int, attach_id int, attach_type text, attach_country text)
;  
INSERT INTO jp_voices
    (voice_id, thread_id, parent_id, voice_type, voice_text, up, down, attach_id, attach_type, attach_country)
VALUES
    (1, 1, 0, 'comment', '元気ですか？',   10, 15, 0, '', ''),
    (2, 1, 1, 'reply',   '元気ですよ！',   29, 28, 4, 'drama', 'en'),
    (3, 2, 0, 'comment', '生きてますか？',  31, 37, 3, 'user', 'ko'),
    (4, 2, 3, 'reply',   '生きてますよ！',  46, 40, 0, '', ''),
    (5, 2, 3, 'reply',   '死んでますよ！',  52, 53, 0, '', ''),
    (6, 2, 5, 'reply',   '死んでますか！？', 69, 60, 0, '', ''),
    (7, 1, 0, 'comment', '愛してますか？',  78, 73, 3, 'user', 'jp')
;

# jp_users
# ユーザーのテーブル
CREATE TABLE jp_users
    (user_id int, unique_name text, nick_name text)
;  
INSERT INTO jp_users
    (user_id, unique_name, nick_name)
VALUES
    (1, 'ichiro', '田中一郎'),
    (2, 'jiro',   '田中二郎'),
    (3, 'saburo', '田中三郎'),
    (4, 'shiro',  '田中四郎'),
    (5, 'goro',   '山田五郎'),
    (6, 'rokuro', '山田六郎'),
    (7, 'nanaro', '山田七郎')
;

###「スレッドへのコメントリスト」
まずこちらが「スレッドへのコメントリスト」になりまして、thread_id=1へのコメントを取得します。

下記コードを実行する

select *
from
jp_voices
where
thread_id=1 and voice_type='comment'

###「お気に入りリスト」
そしてこちらが「お気に入りリスト」になりまして、user_id=1さんのfavoriteを取得します。

下記コードを実行する

        select
               case
                  when actions.target_type='user' and actions.target_country_code='jp'
                  then users.user_id    
                  else contents.content_id
               end as content_id 
             , case
                  when actions.target_type='user' and actions.target_country_code='jp'
                  then users.nick_name  
                  else null
               end as nick_name
             , case
                  when actions.target_type='thread' and actions.target_country_code='jp'
                  then threads.thread_text 
                  else null
               end as thread_text 
             , case
                  when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
                  then null
                  else contents.content_title 
               end as content_title
             , case
                  when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
                  then actions.target_type                
                  else content_type
               end as content_type  
             , case
                  when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
                  then actions.target_country_code     
                  else contents.country_code
               end as country_code 
             , max( case when 
                actions.action_name='follow' then actions.action_date end 
                ) as follow_date
             , max( case when 
                actions.action_name='favorite' then actions.action_date end 
                ) as favorite_date
        
        from  jp_actions actions
        
        # join
        left join jp_threads threads
        on  actions.target_id=threads.thread_id
        left join jp_users users
        on  actions.target_id=users.user_id      
        left join country_contents contents
        on  actions.target_id=contents.content_id 
      
        # 彼がactor_idであるfavoriteを指定
        where actions.actor_id=1
          # favorite_dateとfollow_dateが欲しいので以下を指定
          and actions.action_name in ('favorite', 'follow') 
          and exists(
            select 0 from jp_actions
            where target_id=actions.target_id and action_name='favorite'
          ) 
          # 彼とblock関係にあるjpのuserを除外
          and not exists(
            select 0 from jp_actions
            where target_type='user' and target_country_code='jp' and action_name='block'
            and target_id=actions.target_id and actions.target_type='user'
            and actor_id=1
          ) 
  
        group by actions.target_id, actions.target_type, actions.target_country_code
        order by favorite_date desc

###試したこと
SELECTの結果同士の連結になるかと思いますので、以下の方法を見つけました

SELECT *
FROM
[ﾃｰﾌﾞﾙA]
INNER JOIN
  (
  SELECT [項目B]
  FROM XX
  WHERE XX
  )AS [結果ｾｯﾄC]
ON [ﾃｰﾌﾞﾙA].[項目C] = [結果ｾｯﾄC].[項目B]
WHERE
[ﾃｰﾌﾞﾙA].[項目D] = 'XX' 

そしてこの方法を「スレッドへのコメントリスト」と「お気に入りリスト」に適用したのが下記ですが、実現することができませんでした。

下記コードを実行する → 実現できずエラーでした

SELECT *
FROM
jp_voices
INNER JOIN
  (     
  # 「お気に入りリスト」のコードをここにコピペ
  )AS list
ON jp_voices.attach_id=list.content_id
WHERE
jp_voices.parent_thread_id=1 and voice_type='comment'

###補足情報（FW/ツールのバージョンなど）
phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。

冒頭の画像のような連結のために修正すべき箇所をご指導いただけませんでしょうか。
どうぞ宜しくお願い致します。

Comments

[m.ts10806]

CREATE TABLE文（テーブル定義）とサンプルデータのINSERT文もあったほうがこちらも確認と検証がしやすいのですけど・・・。

[Orlofsky]

バッククォートを使うのをやめちゃったのね。 https://blog.tokumaru.org/2013/12/sql.html

[ikatako]

m.ts10806さん、掲載したつもりでしたが、すみません、質問が読みにくかったですよね。

[ikatako]

Orlofskyさん、大変勉強になりました。ありがとうございます。

[m.ts10806]

ごめんなさい。こちらの見落としでした。

[ikatako]

m.ts10806さん、いえ気にしないでください。あちらの質問とあわせてまた何かあれば遠慮なく仰ってください。

Answer 1

単純にJOINしました。

select * from jp_voices
inner join
( select
    case
    when actions.target_type='user' and actions.target_country_code='jp'
    then users.user_id
    else contents.content_id
    end as content_id
  , case
    when actions.target_type='user' and actions.target_country_code='jp'
    then users.nick_name
    else null
    end as nick_name
  , case
    when actions.target_type='thread' and actions.target_country_code='jp'
    then threads.thread_text
    else null
    end as thread_text
  , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then null
    else contents.content_title
    end as content_title
    , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_type
    else content_type
    end as content_type
  , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_country_code
    else contents.country_code
    end as country_code
  , max( case when
         actions.action_name='follow' then actions.action_date end
    ) as follow_date
  , max( case when
         actions.action_name='favorite' then actions.action_date end
    ) as favorite_date
  from  jp_actions actions
  # join
    left join jp_threads threads
      on  actions.target_id=threads.thread_id
    left join jp_users users
      on  actions.target_id=users.user_id
    left join country_contents contents
      on  actions.target_id=contents.content_id

    # 彼がactor_idであるfavoriteを指定
    where actions.actor_id=1
    # favorite_dateとfollow_dateが欲しいので以下を指定
         and actions.action_name in ('favorite', 'follow')
         and exists(
           select 0 from jp_actions
           where target_id=actions.target_id and action_name='favorite'
         )
    # 彼とblock関係にあるjpのuserを除外
        and not exists(
                select 0 from jp_actions
                where target_type='user' and target_country_code='jp' and action_name='block'
                and target_id=actions.target_id and actions.target_type='user'
                and actor_id=1
                )
    group by 1,2,3,4,5,6
) AS list
ON jp_voices.attach_id=list.content_id
and jp_voices.attach_type=list.content_type
and jp_voices.attach_country=list.country_code
where jp_voices.thread_id=1 and jp_voices.voice_type='comment'
;

メモリ消費軽減SQLを作成してみました

select
    case
    when actions.target_type='user' and actions.target_country_code='jp'
    then users.user_id
    else contents.content_id
    end as content_id
  , case
    when actions.target_type='user' and actions.target_country_code='jp'
    then users.nick_name
    else null
    end as nick_name
  , case
    when actions.target_type='thread' and actions.target_country_code='jp'
    then threads.thread_text
    else null
    end as thread_text
  , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then null
    else contents.content_title
    end as content_title
  , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_type
    else content_type
    end as content_type
  , case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_country_code
    else contents.country_code
    end as country_code
  , max( case when
         actions.action_name='follow' then actions.action_date end
    ) as follow_date
  , max( case when
         actions.action_name='favorite' then actions.action_date end
    ) as favorite_date
  , jp_voices.voice_id
  , jp_voices.thread_id
  , jp_voices.parent_id
  , jp_voices.voice_type
  , jp_voices.voice_text
  , jp_voices.up
  , jp_voices.down
  from  jp_actions actions
  # join
    left join jp_threads threads
      on  actions.target_id=threads.thread_id
    left join jp_users users
      on  actions.target_id=users.user_id
    left join country_contents contents
      on  actions.target_id=contents.content_id
    join jp_voices
    on  jp_voices.attach_id=actions.target_id
    and jp_voices.attach_type=case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_type else content_type end
    and jp_voices.attach_country=case
    when (actions.target_type='user' and actions.target_country_code='jp') or actions.target_type='thread'
    then actions.target_country_code
    else contents.country_code
    end
    and jp_voices.thread_id=1 and jp_voices.voice_type='comment'

   # 彼がactor_idであるfavoriteを指定
    where actions.actor_id=1
    # favorite_dateとfollow_dateが欲しいので以下を指定
         and actions.action_name in ('favorite', 'follow')
         and exists(
           select 0 from jp_actions
           where target_id=actions.target_id and action_name='favorite'
         )
    # 彼とblock関係にあるjpのuserを除外
        and not exists(
                select 0 from jp_actions
                where target_type='user' and target_country_code='jp' and action_name='block'
                and target_id=actions.target_id and actions.target_type='user'
                and actor_id=1
                )
    group by 1,2,3,4,5,6,9,10,11,12,13,14,15;

Comments

[ikatako]

ありがとうございます。メモリの食いすぎということで実現できませんでした。スマートな方法があればいいのですが、長くなると難しいですよね。

ところでgroup byの123456というのは何を意味しているのですか？

[ikatako]

メモリ消費軽減SQL、ありがとうございます。やはりgroup byの数字が理解できないので、hoshi-takanoriさんの理解できるコードに魅力を感じます。もしお時間ございましたら簡単にご説明願えませんでしょうか。

[amura]

group by の数字はselectのカラムの順番号です。caseなどで編集されている場合に結果を元にグループ化を行ってくれます。実際はSQLのコンパイル時にselectに書かれたものをそのまま順番に並べて展開しています。つまり番号を指定して、実行時に対応するSQLを展開して下さいの指定です。
７，８が無いのはMAXに対応する番号です(groupbyの対象外）

[ikatako]

groupbyをマスターしていないので難しいですね。尚、メモリ消費軽減SQLの方も残念ながらメモリの食いすぎというエラーになってしまいましたが、そのselectのまとめ方が大変勉強になりました。ありがとうございます。

Answer 2

WHERE の条件は jp_voices テーブルに対するものなので、こんな感じでどうでしょうか？

SELECT *
FROM
  (
    select *
    from jp_voices
    where thread_id=1 and voice_type='comment'
  ) AS voices
INNER JOIN
  (     
  # 「お気に入りリスト」のコードをここにコピペ
  ) AS list
ON voices.attach_id=list.content_id

INNER JOIN だとお気に入りリストに入ってないコメントは消えてしまうので、OUTER JOIN の方がいいかも。

Comments

[ikatako]

どうもありがとうございます。質問の図説のようにはなりませんでしが、良いヒントを頂戴致しました。参考にさせて頂きます。

[ikatako]

ご回答のコードを
・最後のONに条件をいくつか追加
・LEFT OUTER JOINへ変更
とすることによって、図説のように連結できました。
http://sqlfiddle.com/#!9/78c942/30