SQLでJOINするテーブルをカラムの値によって変えたい

前提

初めて質問させていただきます。
当方IT職ではなく、慣れませんが宜しくおねがいします。

例として以下のようなテーブルがあります。
FOOD_MENUにはご飯のメニューが入っています。

|MENU_ID|NAME|(中略)|REQUIRED_FOOD1|REQUIRED_FOOD2|REQUIRED_FOOD3|
|:--:|:--:|:--:|:--:|:--:|
|1|カレー|...|201|202|301|
|2|焼肉|...|301|302|
|3|白米|...||||

REQUIRED_FOOD1 と 2 と3には必要な食材のIDが格納されています。

VEGETABLE_FOODには野菜データが入っています。

FOOD_ID	NAME	PRICE
201	じゃがいも	100
202	にんじん	100
201	たまねぎ	80

野菜テーブルのFOOD_IDは必ず2で始まります。

MEAT_FOODにはお肉データが入っています。

FOOD_ID	NAME	PRICE
301	豚肉	300
302	牛肉	500

肉テーブルのFOOD_IDは必ず3で始まります。

##実現したいこと
SQLでJOINするテーブルをカラムの値によって変えたいです。

具体的には、REQUIRED_FOODxの左端の値が2であればVEGETABLE_FOODテーブルをJOIN、3であればMEAT_FOODテーブルをJOINといった具合です。
しかし後述するクエリでは、私が求める結果が出ないのです。

試した事・発生している問題

以下のことを試しました。
######LEFT JOINのテーブル名をCASE演算子で指定する

DB2
1SELECT * 
2FROM FOOD_MENU
3
4LEFT JOIN 
5(CASE LEFT(REQUIRED_FOOD1,1)
6WHEN '2' THEN (VEGETABLE_FOOD AS VEGE1 ON FOOD_MENU.REQUIRED_FOOD1 = VEGE1.FOOD_ID) 
7WHEN '3' THEN (MEAT_FOOD AS MEAT1 ON FOOD_MENU.REQUIRED_FOOD1 =  MEAT1.FOOD_ID)
8END)
9LEFT JOIN
10(CASE LEFT(REQUIRED_FOOD2,1)
11WHEN '2' THEN (VEGETABLE_FOOD AS VEGE2 ON FOOD_MENU.REQUIRED_FOOD2 = VEGE2.FOOD_ID) 
12WHEN '3' THEN (MEAT_FOOD AS MEAT2 ON FOOD_MENU.REQUIRED_FOOD2 =  MEAT2.FOOD_ID)
13END)
14
15LEFT JOIN...(以下REQUIRED_FOOD3も同じようにJOIN)

結果：

[IBM]SQL0104 - トークン(は正しくない。有効なトークンはTO OUTER EXCEPTIONです。

エラーメッセージが帰ってきました。

#####必要なテーブルをすべてLEFT JOINする

DB2
1SELECT * 
2FROM PEPOLE 
3
4LEFT JOIN VEGETABLE_FOOD AS VEGE1 ON FOOD_MENU.MENU_ID = VEGE1.FOOD_ID
5LEFT JOIN MEAT_FOOD AS MEAT1 ON FOOD_MENU.MENU_ID = MEAT1.FOOD_ID
6LEFT JOIN VEGETABLE_FOOD AS VEGE2 ON FOOD_MENU.MENU_ID = VEGE2.FOOD_ID
7LEFT JOIN MEAT_FOOD AS MEAT2 ON FOOD_MENU.MENU_ID = MEAT2.FOOD_ID
8
9LEFT JOIN .....(以下REQUIRED_FOOD3も同じようにJOIN)

結果：
|MENU_ID|NAME|(中略)|VEGE1.NAME|MEAT1.NAME|VEGE2.NAME|MEAT2.NAME|VEGE3.NAME|MEAT3.NAME|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|1|カレー|...|じゃがいも||にんじん||||
|1|カレー|...||||||豚肉|
|2|焼肉|...||豚肉||牛肉|||
|3|白米|...|||||||

結果は出るが、1行目と2行目を一緒にしたい。(後述)

##理想
|MENU_ID|NAME|(中略)|VEGE1.NAME|MEAT1.NAME|VEGE2.NAME|MEAT2.NAME|VEGE3.NAME|MEAT3.NAME|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|1|カレー|...|じゃがいも||にんじん|||豚肉|
|2|焼肉|...||豚肉||牛肉|||
|3|白米|...|||||||

MENU_IDを重複させずに、JOINしたテーブルの値も一つのレコードに集めたい（マージさせる？）

言い方が拙くて本当に申し訳ないのですがご教授ください。

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

IBM DB2 VER 9.7

hihijiji

2019/06/08 04:49

まずは、データベースの正規化を行うことをお勧めします。このままではどんどん酷いことになります。

TonToroJP

2019/06/08 05:46

ご指摘ありがとうございます。権限はありませんが正規化についても調べてみようと思います。

行動規範の内容に同意します

回答1件

ベストアンサー

SQLでJOINするテーブルをカラムの値によって変えたいです。

動的にSQLを組み立てない限りできません。
SQLの基本は集合を作成する事です。ケースがあるなら先ず全ての場合を含んだ集合を作ること。

WITH式で纏めて、それを結合

SQL
1with FOOD as (
2   slect * from VEGETABLE_FOOD
3 union all
4   slect * from MEAT_FOOD
5)
6SELECT MENU.*, FOOD1.NAME, FOOD2.NAME, FOOD3.NAME  
7FROM FOOD_MENU AS MENU 
8     LEFT JOIN FOOD AS FOOD1 ON MENU.REQUIRED_FOOD1= FOOD1.FOOD_ID
9     LEFT JOIN FOOD AS FOOD2 ON MENU.REQUIRED_FOOD2= FOOD2.FOOD_ID
10     LEFT JOIN FOOD AS FOOD3 ON MENU.REQUIRED_FOOD3= FOOD3.FOOD_ID

--
相関副問合せで行う。
FOOD_MENU の件数が少なければ（where条件で絞り込まれる場合も含む）こちらの方が高速。

SQL
1SELECT MENU.*
2     , coalesce(
3         (select NAME from VEGETABLE_FOOD where FOOD_ID=MENU.REQUIRED_FOOD1)
4        ,(select NAME from MEAT_FOOD where FOOD_ID=MENU.REQUIRED_FOOD1)
5       ) FOOD1_NAME
6     , coalesce(
7         (select NAME from VEGETABLE_FOOD where FOOD_ID=MENU.REQUIRED_FOOD2)
8        ,(select NAME from MEAT_FOOD where FOOD_ID=MENU.REQUIRED_FOOD2)
9       ) FOOD2_NAME
10     , coalesce(
11         (select NAME from VEGETABLE_FOOD where FOOD_ID=MENU.REQUIRED_FOOD3)
12        ,(select NAME from MEAT_FOOD where FOOD_ID=MENU.REQUIRED_FOOD3)
13       ) FOOD3_NAME
14FROM FOOD_MENU AS MENU