複数を抽出したい。

質問

menu テーブル
ID 1 / NAME ご飯
ID 2 / NAME 味噌汁
ID 3 / NAME 焼しゃけ
ID 4 / NAME 焼さば
ID 5 / NAME 肉じゃが

であり

setmeny テーブル
ID 1 / NAME 焼しゃけ定食 / M1_ID 1 / M2_ID 2 / M2_ID 3
ID 2 / NAME 焼さば　定食 / M1_ID 1 / M2_ID 2 / M2_ID 4
ID 3 / NAME 肉じゃが定食 / M1_ID 1 / M2_ID 2 / M2_ID 5

とデータを入力してあるとき

1 / 焼しゃけ定食 / ご飯 / 味噌汁 / 焼しゃけ
2 / 焼さば　定食 / ご飯 / 味噌汁 / 焼さば
3 / 肉じゃが定食 / ご飯 / 味噌汁 / 肉じゃが

という表を取り出す方法はありますか？

select
setmenu.ID,
setmenu.NAME,
menu.NAME
from
setmenu join enu
where
setmenu.M1_ID = menu.ID;

で

1 / 焼しゃけ定食 / ご飯
2 / 焼さば　定食 / ご飯
3 / 肉じゃが定食 / ご飯

までは出せるのですが。

/ 味噌汁 / 焼しゃけ
/ 味噌汁 / 焼さば
/ 味噌汁 / 肉じゃが

をどう出して良いかわかりません。

またはもっと効率が良い方法あるのでしょうか？

回答No.2

yambejp

>量も扱う必要が出てきました。

ではsetmenuテーブルを拡張して量をいれてみてください。

create table menu(id int,name varchar(30));
insert into menu values(101,'ご飯'),(102,'味噌汁'),(103,'焼しゃけ'),(104,'焼さば'),(105,'肉じゃが'), (106,'漬物')
,(201,'焼しゃけ定食'),(202,'焼さば　定食'),(203,'肉じゃが定食'),(204,'肉じゃが定食 大盛');

create table setmenu(setmenu_id int,menu_id int,quantity double);
insert into setmenu values(201,101,1),(201,102,1),(201,103,1),(201,106,1)
,(202,101,1),(202,102,1),(202,104,1),(202,106,1)
,(203,101,1),(203,102,1),(203,105,1.5),(203,106,1)
,(204,101,2),(204,102,1),(204,105,2),(204,106,1);

select setmenu_id ,sm.name as setname ,group_concat(m.name ,' ',CAST(quantity AS CHAR) ) as setmenu
from setmenu as s
inner join menu as m on menu_id=m.id
inner join menu as sm on setmenu_id=sm.id
group by setmenu_id;

回答No.1

yambejp

このデータの持ち方ですと、３回JOINするのが妥当ですね

select s.id,s.name,m1.name,m2.name,m3.name
from setmenu as s
left join menu as m1 on m1.id=M1_ID
left join menu as m2 on m2.id=M2_ID
left join menu as m3 on m3.id=M3_ID

ただしかならず３個の要素だというものではありませんので
データ管理上横方向に要素を持つことは効率的とは言えません。
こんな風な処理でもいいかもしれません。

create table element(id int,name varchar(30));
insert into element values(1,'ご飯'),(2,'味噌汁'),(3,'焼しゃけ'),(4,'焼さば'),(5,'肉じゃが');

create table menu(id int,name varchar(30));
insert into menu values(1,'焼しゃけ定食'),(2,'焼さば　定食'),(3,'肉じゃが定食');

create table setmenu(menu_id int,element_id int);
insert into setmenu values(1,1),(1,2),(1,3),(2,1),(2,2),(2,4),(3,1),(3,2),(3,5);

select menu_id,m.name as menu,group_concat(e.name order by e.id) as element
from setmenu as s
inner join menu as m on menu_id=m.id
inner join element as e on element_id=e.id
group by menu_id

お礼

なるほど、複数回joinする必要があるのですね。また、セットメニューの管理はその方が拡張性がありますね。ありがとうございました。

単品でもメニュー、セットメニューもメニューであり、テーブルを3つ持つと管理がたいへんになるので、テーブルを2つにして下記のようにしました。

create table menu(id int,name varchar(30));
insert into menu values(101,'ご飯'),(102,'味噌汁'),(103,'焼しゃけ'),(104,'焼さば'),(105,'肉じゃが'),(106,'漬物')
,(201,'焼しゃけ定食'),(202,'焼さば　定食'),(203,'肉じゃが定食');

create table setmenu(setmenu_id int,menu_id int);
insert into setmenu values(201,101),(201,102),(201,103),(201,106)
,(202,101),(202,102),(202,104),(202,106)
,(203,101),(203,102),(203,105),(203,106);

select setmenu_id ,sm.name as setname ,group_concat(m.name order by m.id) as setmenu
from setmenu as s
inner join menu as m on menu_id=m.id
inner join menu as sm on setmenu_id=sm.id
group by setmenu_id;

補足

これでいいかと思いましたが、

肉じゃが定食は、ご飯1人前、味噌汁1人前、肉じゃが1.5人前、漬物1人前で、量も扱う必要が出てきました。

203 肉じゃが定食 ご飯 1 ,味噌汁 1 ,肉じゃが 1.5 ,漬物 1

まだ解決できていませんでした。