外部結合で結合できないデータも取りたいです

Question

前提・実現したいこと

初めまして、SQL初心者です。

早速ですが、
A(グループ),B(生徒),C(性別)というテーブルがあるとします。

AとBは1:Nの関係で、
BとCは1:1の関係です。

• A(グループ)テーブル

id(pk)	b_id(pk)
1	1
1	2
1	3
2	4
3	5

• B(生徒)テーブル

id(pk)	c_id
1	1
2	1
3	2
4	1
5	1

• C(性別)テーブル

id(pk)	sex
1	men
2	women

発生している問題・エラーメッセージ

上記のテーブル構成で、グループに存在しない性別の生徒も含めて全レコードを取得したいのですが、SQL一回で取得できるものなのでしょうか。

• 期待する結果

a_id	b_id	c_id	name
1	1	1	men
1	2	1	men
1	3	2	women
2	4	1	men
2	null	2	women
3	5	1	men
3	null	2	women

試したこと

-- Aテーブル作成
create table A (id integer, b_id integer);
insert into A values (1,1);
insert into A values (1,2);
insert into A values (1,3);
insert into A values (2,4);
insert into A values (3,5);

-- Bテーブル作成
create table B (id integer, c_id integer);
insert into B values (1,1);
insert into B values (2,1);
insert into B values (3,2);
insert into B values (4,1);
insert into B values (5,1);

-- Cテーブル作成
create table C (id integer, name varchar(10));
insert into C values (1, 'men');
insert into C values (2, 'women');

外部結合なら結合できなかったものも取れるということでしたので、試して見ましたが
取得できませんでした・・

select A.id a_id, B.id b_id, C.id c_id, C.name 
from A inner join B on A.b_id = B.id 
right outer join C on C.id = B.c_id 
order by A.id,C.id;

+------+------+------+-------+
| a_id | b_id | c_id | name  |
+------+------+------+-------+
|    1 |    1 |    1 | men   |
|    1 |    2 |    1 | men   |
|    1 |    3 |    2 | women |
|    2 |    5 |    1 | men   |
|    2 |    4 |    1 | men   |
+------+------+------+-------+

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

mysql 5.7を使用しております。

ご教授のほどよろしくお願い致します。

Comments

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされている必要があります。テーブル定義の説明もCREATE TABLEで載せると適切なコメントが付き易いです。同じようにSELECTされるデータもINSERTされている必要がありますから、INSERT文で載せておくとコメントが付き易いです。

[erohermit]

コメントありがとうございます。失礼しました。create文、insert文を載せて起きました。

[m.ts10806]

質問編集画面タイトル横にある「初心者アイコン」をご活用ください。「初心者」と質問で書くよりも伝わりますし、質問一覧に表示されるのでわかりやすくなります。

[erohermit]

教えていただき、ありがとうございます。活用します

Answer 1

テーブル構造が微妙な気がします。
AとBが1:Nの関係ならば、BテーブルにグループID列を定義すべきです。
あと、BとCは1:1という説明がありますが、N:1な気がします。

Answer 2

生徒を優先したいならBからAに外部結合する必要があります
但し、Aが取得できない場合は当然A.idはnullになります。
ですので、並び順も工夫が必要です。

select  A.id a_id, B.id b_id, C.id c_id, C.name 
from    B left join A 
        on A.b_id = B.id 
        left outer join C 
        on C.id = B.c_id 
order by coalesce(A.id, b.id), C.id

※サンプルデータだと５件にしかならないので、差分の２行は何がしたいのかな？

a_id	b_id	c_id	name
1	1	1	men
1	2	1	men
1	3	2	women
2	4	1	men
3	5	1	men

追記

aとcの直積(full join)をグループidと性別でグルーピングしたものを基準に、
aとｂを結合したものに外部結合します。

select ac.a_id, ab.b_id, ac.c_id, ac.name
from    (
          select  a.id as a_id, c.id as c_id, c.name 
          from    a, c 
          group by a.id, c.id, c.name
        ) ac left join (
          select a.id as a_id, a.b_id, b.c_id
          from    A inner join b 
                  on  a.b_id=b.id
        ) ab
        on  ac.a_id=ab.a_id and ac.c_id=ab.c_id
order by  ac.A_id, ab.b_id, ac.c_id

ポイントになるのは、基準となるものを如何に作り出すかと言う事ですね。
後はその基準に一度に結合する条件を揃える整形をしたものと結合する。

Comments

[erohermit]

回答ありがとうございます。
> Aが取得できない場合は当然A.idはnull
やっぱりこれは、どうしようもできないものでしょうか・・

[sazi]

どうしたいんですか？ルールが有るなら作り出すことも可能ではありますが。

[erohermit]

各グループに男女が存在するしないをリストで取りたいと思っています。
saziさんの結果の表で、a.idがnullのところにそれぞれ、2と3が入ったリストが取れると良いのです。

[sazi]

追記しました。あ、駄目か、ちょっと修正します。

[sazi]

修正しました。ちょっと面倒ですね。

[erohermit]

追記確認致しました。
期待する結果が取得できることを確認できました！

> 基準となるものを如何に作り出すかと言う事
大変勉強になります。 full joinに関してもまだまだ知識がないのでいろいろ調べてみようかと思います！

ありがとうございました。
今後ともよろしくお願いします。

Answer 3

単純な SQL では無理ですね。
問題は「存在しない性別の生徒のデータをでっち上げる」ことにあるのですが、外部結合で導き出せる類いのものではありません。
やりたいことを順に考えると、

1. あるグループに所属する生徒の一覧を、「男女別に」取り出す（この結果を t1, t2 とする）
2. count(t1) = 0 の場合、b.id = null かつ c.id = 1 になるレコードをでっちあげ、t1 の変わりとする。count(t2) = 0 の場合も同様（c.id=2）
3. t1 と t2 を union する

といった処理をする必要がありそうです。
一つの SQL で書けないわけではないのですが、変に複雑にするよりはロジックで対処した方がよいように思います。

Comments

[erohermit]

コメントありがとうございます。
時にはロジックで対処した方が良い場合もあるということですね。