求教一个查询方法
有两张表,大意如下
A表
ID NAME
------------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
B表
ROOTID SUBID
----------------
1 2
1 3
4 5
4 6
请教一种方法,使结果变成如下:
ROOTNAME SUBNAME
--------------------
AA BB
AA CC
DD EE
DD FF
[解决办法]
selct *
from a a1
left join b on a1.id = b.rootid
left join a a2 on a2.id=b.subid
[解决办法]
selct a1.NAME as ROOTNAME,a2.NAME as SUBNAME
from a a1
right join b on a1.id = b.rootid
left join a a2 on a2.id=b.subid