MySQLのテーブル結合（複数キーが有る場合）

Question

COMPANY
+------+--------+---+
| ID | name |
+------+--------+---+
| 1 | あか |
| 2 | あお |
| 3 | きいろ |
| 4 | みどり |
+------+--------+---+

MEMBER
+------+--------+----------+
| ID | company1 | company2|
+------+--------+----------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 4 | 4 |
| 4 | 3 | 1 |
+------+--------+----------+

というテーブルがあった場合に

結合で、以下のようにデータを取り出したいのですが、可能でしょうか。
+------+--------+----------+-------+
| ID | company1_name | company2_name |
+------+--------+----------+-------+
| 1 | あか | あお |
| 2 | あお | きいろ |
| 3 | みどり | みどり 　|
| 4 | きいろ | あか 　|
+------+--------+----------+-------+

Answer 1

普通に2度joinしてください

select t1.ID
,t2.name as company1_name
,t3.name as company2_name
from MEMBER as t1
inner join COMPANY as t2 ON t1.company1=t2.ID
inner join COMPANY as t3 ON t1.company2=t3.ID
order by ID

どうしても1回のjoinで処理したいときは少し複雑になります

select t1.ID
,group_concat(case t1.company1 when t2.ID then t2.name end) as company1_name
,group_concat(case t1.company2 when t2.ID then t2.name end) as company2_name
from MEMBER as t1
inner join COMPANY as t2 ON t1.company1=t2.ID or t1.company2=t2.ID
group by t1.ID;

Comments

[falcon0321]

ありがとうございました！勉強になりました！！