sql关联三个表查询的问题

Question

表a和表b 通过两个查询分别得到以下两组数据

1，select e.code ,e.name from code d,name e where e.id=d.id and e.type="球类"

2，select e.code ,e.name from code d,name e where e.id=d.id and e.type="棋类"

code name
01 足球
02 篮球
03 羽毛球

code name
101 围棋
102 象棋
103 五子棋

表c 学生表 字段如下

学号 姓名 …… 喜欢的球类运动 喜欢的棋类运动
1 张三 01 101

如何关联表a表b查询学生表 并显示的正确球类运动，棋类运动

Answer 1

select codeo,nameo,namea,nameb,namec from
(select e.code codeo ,e.name nameo from code d,name e where e.id=d.id) as o

(select e.code codea,e.name namea from code d,name e where e.id=d.id and e.type="球类1") as a
on o.id=a.id
left join
(select e.code codeb ,e.name nameb from code d,name e where e.id=d.id and e.type="球类2") as b
on o.id=b.id
left join
(select e.code codec ,e.name namec from code d,name e where e.id=d.id and e.type="球类c") as c
on o.id=c.id