mysqlの結合について教えてください

`table_m
"id","name","partid1","partid2"
"1","桜","1","2"
"2","鈴","4","1"
"3","田","2","1"
"4","中","3",null
と
`table_p
"partid","name"
"1","vocal"
"2","guitar"
"3","bass"
"4","drums"
"5","keyboard"
を結合して

`実現したい結合
"id","name","partid1","partid2","name","name"
"1","桜","1","2","vocal","guitar"
"2","鈴","4","1","drums","vocal"
"3","田","2","1","guitar","vocal"
"4","中","3",null,"bass",null

のようなテーブルを作成したいのですがどのようにすればいいかわからずに困っています。
ご教示よろしくお願いします。

No.1 ベストアンサー 回答者： Ogre7077 回答日時： 2022/05/19 15:46

左側を基準とする外部結合 (LEFT OUTER JOIN) を使いましょう。

例)
from table_m M
left outer join table_p P1 on M.partid1 = P1.partid
left outer join table_p P2 on M.partid2 = P2.partid

参考)
https://dev.mysql.com/doc/refman/8.0/ja/join.html

この回答へのお礼

参考サイトまでありがとうございます。
outerが分かっていませんでした。
無事実現できました。

お礼日時：2022/05/19 15:52