【SQL】複数のテーブルを条件付きで結合したい

Question

前提

MySQL 5.7

ユーザが気に入ったコミックを投票するシステムがあり、以下のテーブル構造を持っています。

ユーザの属性は一般ユーザと管理ユーザの2種類あり、usersのadmin（1が入っていれば管理者）カラムで管理しています。

実現したいこと

雑誌と、その雑誌が持つコミックの内、最も管理者ユーザに投票されたコミック1件を結合させて取得するSQLを教えていただきたいです。

結合するイメージとしては以下です。
結合したいテーブルに色を付けています。

雑誌A - コミックA：コミックAが最も管理者ユーザからの投票が多いため
雑誌B - コミックC：コミックCが最も管理者ユーザからの投票が多いため
雑誌C - なし：管理者ユーザの投票がないため、取得しない

サンプル

-- Create
CREATE TABLE users (
    id integer,
    name varchar(100),
    admin integer
);

CREATE TABLE votes (
    id integer,
    comic_id integer,
    user_id integer
);

CREATE TABLE comics (
    id integer,
    magazine_id integer,
    name varchar(100)
);

CREATE TABLE magazines (
    id integer,
    name varchar(100)
);

-- Insert
-- ユーザ
insert into users(id, name, admin) values(1, "一般ユーザA", 0);
insert into users(id, name, admin) values(2, "管理者ユーザA", 1);

-- 雑誌
insert into magazines(id, name) values(1, "雑誌A");
insert into magazines(id, name) values(2, "雑誌B");
insert into magazines(id, name) values(3, "雑誌C");

-- コミック
insert into comics(id, magazine_id, name) values(1, 1, "コミックA");
insert into comics(id, magazine_id, name) values(2, 1, "コミックB");
insert into comics(id, magazine_id, name) values(3, 2, "コミックC");
insert into comics(id, magazine_id, name) values(4, 3, "コミックD");

-- 投票
insert into votes(id, comic_id, user_id) values(1, 1, 1);
insert into votes(id, comic_id, user_id) values(2, 1, 2);
insert into votes(id, comic_id, user_id) values(3, 1, 2);
insert into votes(id, comic_id, user_id) values(4, 2, 1);
insert into votes(id, comic_id, user_id) values(5, 2, 1);
insert into votes(id, comic_id, user_id) values(6, 2, 1);
insert into votes(id, comic_id, user_id) values(7, 3, 1);
insert into votes(id, comic_id, user_id) values(8, 3, 2);
insert into votes(id, comic_id, user_id) values(9, 4, 1);

補足

• 管理者ユーザが投票したコミックが存在しなれば、その雑誌は取得しない

Comments

[yambejp]

votesのid=2とid=3が同じ(comic_id=1/ user_id=2)なんですが大丈夫ですか？
これは同じ人間が同じコミックを何度も投票できるということですので、
不正投票の温床になります。

かりに上記が良かったとしてcomic_id=1と2の投票数が同じ3票ですが
これはどちらが優先されるのでしょうか？(両方受け取りたい？)
、

[babybaby]

> 同じ人間が同じコミックを何度も投票
どこかでガードしなければなりませんが、本筋とずれるので一旦許容するとさせてください。

> comic_id=1と2の投票数が同じ3票ですがこれはどちらが優先
最も管理者ユーザ（userのadminが1）に投票されたコミックを抽出したいので、comic_id=1を優先でお願いします。

Answer 1

先ほどwindow関数を使ったSQLにて誤って回答してしまいました。
以下あらためて、再度訂正して回答します。

select m.name,b.comic_name from magazines m
inner join
(
select distinct a.comic_name,a.magazine_id from (
select w.comic_name,w.magazine_id,w.id,w.user_name,count(*) from
(
select
c.name as comic_name
,c.magazine_id
,v.user_id as kind
,u.id
,u.name as user_name
,u.admin as admin
from comics c
inner join votes v on c.id = v.comic_id
inner join users u on v.user_id = u.id
) w where w.admin = 1
group by w.comic_name,w.magazine_id,w.id,w.user_name
) a
) b
on m.id = b.magazine_id
;

+---------+---------------+
| name | comic_name |
+---------+---------------+
| 雑誌A | コミックA |
| 雑誌B | コミックC |
+---------+---------------+
2 rows in set (0.00 sec)

mysql>

Answer 2

「雑誌と、その雑誌が持つコミックの内、最も管理者ユーザに投票されたコミック1件を結合させて取得する」の意味がわからないのですが、voteのcomic_idでgroup by してcountすれば投票が多いコミックはわかります

追記

複数のコミックのうち、最も投票数の多いコミックと雑誌を結合させ

select comic_id,count(*) as cnt from votes group by comic_id order by cnt desc limit 1

で最大の投票されたcomi_idは拾えますが
comicsテーブルにcomic_idが含まれてないので集計のしようがないですね

Comments

[babybaby]

雑誌は複数のコミックを持っており、その複数のコミックのうち、最も投票数の多いコミックと雑誌を結合させたい。という意味でした。
もしよろしければ実際のSQLもご教授頂けませんでしょうか？

[yambejp]

テーブルが中途半端なのでいまの情報からでは集計は困難です。
ER図ではなく具体的なリレーションがわかるようなサンプルを提示されたほうがよいとおもいます

[babybaby]

サンプルを追加しました。

> comicsテーブルにcomic_idが含まれてないので集計のしようがない
失礼しました。ER図には明記していませんが、すべてのテーブルでユニークな「id」カラムを持ちます。

[yambejp]

であれば、提示したクエリをサブクエリにして、comicsテーブルにjoin、さらにそれをmagazinesテーブルにjoinすればよいでしょう

[babybaby]

ご提示下さったサブクエリの場合、limit 1を指定しているためグループ化されたコミックの中から1件しか取得されないのではないでしょうか？
サンプルでいうと、雑誌AとコミックAの結合のみになると思います。雑誌BとコミックCも同時に結合して取得するような記述が望ましいです。

[yambejp]

雑誌ごとに最大投票数のコミックを抽出したいということですかね・・・
sampleは図ではなくcreate table+insertで示してもらうと回答がぶれづらいです

[babybaby]

> 雑誌ごとに最大投票数のコミックを抽出
わかりにくくて申し訳ないです、そういうことです。

サンプルを追加しました。