SQLでカラムの値によってJOINするテーブルを切り分けたい

Question

環境

データベース：MySQL

やりたいこと

■下記のようなテーブルがあります。

＜商品マスタ＞

商品名	商品ID	ジャンル
タケノコ	1	野菜
ミカン	2	果物

＜野菜_価格_テーブル＞

商品ID	価格
1	1,100
2	2,200

＜果物_価格_テーブル＞

商品ID	価格
1	12,100
2	23,200

この時、「商品マスタ.ジャンル」によって結合するテーブルを切り替えたいです。

---

完成想定図は以下の通りです。

＜結合後のテーブル＞

商品マスタ.商品名	商品マスタ.商品ID	商品マスタ.ジャンル	価格テーブル.商品ID	価格テーブル.価格
タケノコ	1	野菜	1	1,100
ミカン	2	果物	2	23,200

SQL

CASE文がFROM内でも使えると聞き、下記のようにしましたが、エラーとなってしまいました。

■参考サイト：SQLでJOINするカラムを条件分岐させようとした話

SELECT * 
FROM `商品マスタ` AS `master`
LEFT JOIN 
(CASE
 WHEN `master`.`ジャンル`='野菜' THEN `野菜_価格_テーブル`
 ELSE `果物_価格_テーブル`
END) AS `価格テーブル`
ON `価格テーブル`.`商品ID`=`master`.`商品ID`

どう修正したら、1回のSQLで、かつ、結合先のテーブルを切り分けることができるのでしょうか？

Answer 1

そもそもテーブルを分けてしまったのがネックですね。
下記のようなテーブルにしておけばジャンルと商品IDで結合できました。

<価格テーブル>

ジャンル	商品ID	価格
野菜	1	1,100
野菜	2	2,200
果物	1	12,100
果物	2	23,100

現状のテーブルのままでいくなら、UNIONで上記のように変換してから、結合ですね。

SELECT * 
FROM 商品マスタ AS master
LEFT JOIN 
(SELECT '野菜' AS ジャンル, 商品ID, 価格
 FROM 野菜_価格_テーブル
 UNION ALL
 SELECT '果物' AS ジャンル, 商品ID, 価格
 FROM 果物_価格_テーブル
) AS 価格テーブル
ON 価格テーブル.ジャンル=master.ジャンル AND 価格テーブル.商品ID=master.商品ID

Comments

[cobm]

なるほど、価格テーブルまとめた方が良さそうですね。

[meex]

ご回答ありがとうございます。今後、ジャンルが増減した場合に切り離しや追加がしやすいようにという設計思想から、テーブルを分けることとなりました。
しかしながら、分離の仕方によっては結合時にすこし難点があることを知りました。今回はひとまずUNIONで対処する運びに致しましたが、今後のDB設計に活かして行きたく存じます。

Answer 2

参考サイトもFROMでCASEを使っていませんし、CASE文がFROM内で使うことは出来なかったかと思います。

必要なテーブルを全てJOINしてから、CASEで参照カラムを分岐させるしかないのかなと。

SELECT `商品マスタ`.`商品名`,
       `商品マスタ`.`商品ID`,
       `商品マスタ`.`ジャンル`,
       (CASE `商品マスタ`.`ジャンル` WHEN '野菜' THEN `野菜_価格_テーブル`.`価格` 
　　　　　　　　　　　　　　　　ELSE `果物_価格_テーブル`.`価格` END) AS `価格テーブル.価格`,
FROM `商品マスタ` AS `master`
LEFT JOIN `野菜_価格_テーブル` ON `商品マスタ`.`商品ID`=`野菜_価格_テーブル`.`商品ID`
LEFT JOIN `果物_価格_テーブル` ON `商品マスタ`.`商品ID`=`果物_価格_テーブル`.`商品ID`;

Comments

[meex]

ご指摘ありがとうございます。間違った知識を修正できました。
CASEの使い方について理解を深めることができました。
今回は都合上、UNIONを使う方式を採用いたしましたが、根本的なDB設計がまずかったことを思い知らされました。次回以降の設計時に活かして行きたく存じます。
ご回答いただけてありがとうございました。