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

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

ただいまの
回答率

90.35%

  • MySQL

    6143questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

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

解決済

回答 3

投稿 編集

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

erohermit

score 1

 前提・実現したいこと

初めまして、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を使用しております。

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • Orlofsky

    2018/06/06 16:56

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

    キャンセル

  • erohermit

    2018/06/06 17:05

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

    キャンセル

  • mts10806

    2018/06/06 17:10

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

    キャンセル

  • erohermit

    2018/06/06 17:22

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

    キャンセル

回答 3

checkベストアンサー

+1

生徒を優先したいなら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


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

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とbを結合したものに外部結合します。

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


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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/06 17:09

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

    キャンセル

  • 2018/06/06 17:10

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

    キャンセル

  • 2018/06/06 17:21

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

    キャンセル

  • 2018/06/06 17:36 編集

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

    キャンセル

  • 2018/06/06 18:11

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

    キャンセル

  • 2018/06/06 18:31

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

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

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

    キャンセル

+1

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

単純な 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 で書けないわけではないのですが、変に複雑にするよりはロジックで対処した方がよいように思います。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/06 19:17

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

    キャンセル

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

  • MySQL

    6143questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。