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

Question

###前提・実現したいこと
以下の条件の時にテーブルの結合条件がわかりません。
図書テーブルと購入者テーブルを結合して、購入者が分類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   |

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

Answer 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 |

Comments

[ozaking]

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

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

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

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

[A.Ichi]

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

[A.Ichi]

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

Answer 2

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

手が止まってしまったというのは、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 |

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

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

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

Comments

[ozaking]

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

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

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 |

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

[ozaking]

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

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

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

となります。

[ozaking]

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

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

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

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