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

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

ただいまの
回答率

87.60%

テーブルの結合条件がわからない。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,789

score 25

前提・実現したいこと

以下の条件の時にテーブルの結合条件がわかりません。
図書テーブルと購入者テーブルを結合して、購入者が分類Aのどの本を、分類Bのどの本を購入したかがわかる抽出後のテーブルを出したいです。

#前提となるテーブル

図書テーブル
|管理番号|分類|購入者コード|
|AA001  |A   |00001      |
|AA002  |A   |00001      |
|BB001  |B   |00001      |
|BB002  |B   |00001      |
|BB003  |B   |00001      |
|CC001  |C   |00002      |

購入者テーブル
|購入者コード|購入者名|
|00001      |やまだ   |
|00002      |あおき   |

上記のようなテーブルがある場合に以下のように抽出したいです。

#抽出後のテーブル

|購入者コード|図書分類A|図書分類B|
|00001      |AA001    |BB001    |
|00001      |AA002    |BB002    |
|00001      |null     |BB003    |

発生している問題

今回試したSQLは以下になります。

SELECT
  購入者テーブル.購入者コード
  , 分類A.図書分類A
  , 分類B.図書分類B 
FROM
  購入者テーブル 
  LEFT OUTER JOIN ( 
    SELECT
      購入者コード AS A_購入者コード
      , 分類 AS 図書分類A 
    FROM
      図書テーブル 
    WHERE
      分類 = 'A'
  ) AS 分類A 
    ON 図書テーブル.購入者コード = 分類A.A_購入者コード 
  LEFT OUTER JOIN ( 
    SELECT
      購入者コード AS B_購入者コード
      , 分類 AS 図書分類B 
    FROM
      図書テーブル 
    WHERE
      分類 = 'B'
  ) AS 分類B 
    ON 図書テーブル.購入者コード = 分類B.B_購入者コード 
WHERE
  図書テーブル.購入者コード = '00001'

これを流すと、以下のように取得されてしまい、ここから手が止まってしまいました。

|購入者コード|図書分類A|図書分類B|
|00001    |AA001   |BB001   |
|00001    |AA002   |BB001   |
|00001    |AA001   |BB002   |
|00001    |AA002   |BB002   |
|00001    |AA001   |BB003   |
|00001    |AA002   |BB003   |

どのような条件にすればよいかご教授いただければと思います。
よろしくお願いいたします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

+1

提示されている出力であれば下記にて取得できると思います。

SELECT coalesce(a.購入者コード,b.購入者コード) 購入者コード,
 a.管理番号 図書分類A, b.管理番号 図書分類B from
(SELECT ROW_NUMBER() OVER (),購入者コード,管理番号 from tosho where 分類='A') a
FULL JOIN
(SELECT ROW_NUMBER() OVER (),購入者コード,管理番号 from tosho where 分類='B') b
USING (row_number) WHERE coalesce(a.購入者コード,b.購入者コード)='00001';

さらに右に図書分類cとはなりません。

ご期待に沿えずにすみませんでした。思い付くところを修正してみました。

SELECT coalesce(a.購入者コード,b.購入者コード) 購入者コード, a.管理番号 図書分類A, b.管理番号 図書分類B from
(SELECT ROW_NUMBER() OVER (),購入者コード,管理番号 from (select * from 図書テーブル order by 管理番号) 図書テーブル where 分類='A') a
FULL JOIN
(SELECT ROW_NUMBER() OVER (),購入者コード,管理番号 from (select * from 図書テーブル order by 管理番号) 図書テーブル where 分類='B') b
USING (row_number) WHERE coalesce(a.購入者コード,b.購入者コード)='00001';

分類の番号のバージョンを作成しました

select coalesce(a.購入者コード,b.購入者コード) 購入者コード, a.管理番号 図書分類A, b.管理番号 図書分類B from
(select substr(管理番号,3), * from  図書テーブル where 分類='A') a
full join
(select substr(管理番号,3), * from  図書テーブル where 分類='B') b
using (substr) where coalesce(a.購入者コード,b.購入者コード)='00001' order by substr;

これだと下記の様になります。
 購入者CD |図書分類A|図書分類B
----------+-------+-------
 00001    | AA001 | BB001
 00001    | AA002 | BB002
 00001    |       | BB003
 00001    | AA004 |

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/10/21 11:41

    ご回答ありがとうございます。

    ご提示いただきましたSQLを流してみたところ、分類Bで想定と違う値が取れてしまいました。
    試行錯誤してみたのですが、解決できず。

    上記コメントにも書いたのですが、
    個別に取得して処理側でカバーする方法をとることにしました。

    せっかくご提示いただいたのにすみません。

    キャンセル

  • 2016/10/21 12:28

    分類Bの並びをソートしました。

    キャンセル

  • 2016/10/21 13:01

    管理でマッチングしてみました。

    キャンセル

checkベストアンサー

0

まず、「実現したいこと」と「#抽出後のテーブル」が、少なくとも私には合ってないと思ったので、
一旦置いておきます。
#図書テーブルが、「実現したいこと」と一致してる気がします。
#「実現したいこと」は・・何を出力したいか、よくわかりません。

手が止まってしまったというのは、SQLの仕組みの理解途中と思われますが、

SELECT
購入者テーブル.購入者コード
, 分類A.図書分類A
, 分類B.図書分類B 
FROM
購入者テーブル 
LEFT OUTER JOIN ( 
SELECT
購入者コード AS A_購入者コード
, 分類 AS 図書分類A 
FROM
図書テーブル 
WHERE
分類 = 'A'
) AS 分類A 
ON 図書テーブル.購入者コード = 分類A.A_購入者コード 

これで、

|購入者コード|図書分類A|図書分類B|
|00001    |AA001   |
|00001    |AA002   |

これになるのは、分かりますか?

何か事情があるかもしれませんが、そもそも例のテーブルが正規化されてないのが、
理解を妨げている要因かもしれません。

図書テーブルは、
・図書管理テーブル[管理番号][分類](主キ-:管理番号)
・図書購入実績[購入者コード][管理番号](主キー:購入者コード、管理番号)
に分割すべきです。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/10/21 10:54

    ご回答ありがとうございます。
    確かに実現したいことがはっきりしていませんでした。

    分類別に抽出はできているので、
    結局のところは、以下のことが実現したいことになります。

    SQL
    SELECT 購入者コード, 管理番号 AS 図書分類A FROM 図書テーブル WHERE 分類 = ”A” AND 購入者コード = ”00001”

    分類A(抽出後)
    |購入者コード|図書分類A|
    |00001 |AA001 |
    |00001 |AA002 |


    SQL
    SELECT 購入者コード, 管理番号 AS 図書分類B FROM 図書テーブル WHERE 分類 = ”B” AND 購入者コード = ”00001”

    分類B(抽出後)
    |購入者コード|図書分類B|
    |00001 |BB001 |
    |00001 |BB002 |
    |00001 |BB003 |

    この個別に抽出した、分類Aテーブルと分類Bテーブルを
    購入者コードで紐づけて、一つの表にしたいです。

    その結果として、
    |購入者コード|図書分類A|図書分類B|
    |00001 |AA001 |BB001 |
    |00001 |AA002 |BB002 |
    |00001 |null |BB003 |

    このようになると想定しています。

    キャンセル

  • 2016/10/21 11:22

    追記:
    購入者テーブルの役割としては、購入者テーブルにある購入者の情報のみを抽出するために使用します。

    なので、コメントに書いたSQLは、厳密には、

    SELECT * FROM 
     購入者テーブル
     LEFT OUTER JOIN (SELECT 購入者コード, 管理番号 AS 図書分類A FROM 図書テーブル WHERE 分類 = ”A”)AS A
     ON 購入者テーブル.購入者コード = A.購入者コード 
    WHERE 購入者コード = ”00001”

    となります。

    キャンセル

  • 2016/10/21 11:35

    解決策として、個別に取得して処理側でカバーする方法で落ち着きました。
    お手数をおかけいたしました。

    理由としては、SQLが複雑になりメンテしにくくなる。
    ここまで複雑にして1度のSQLで流すメリットがあまりない。
    などです。

    私の感覚ではもっと簡単にできるのではと思っていたのですが、甘かったようです。

    ありがとうございました。

    キャンセル

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

  • ただいまの回答率 87.60%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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