请问sql语句

[arclala]

表A

id product pm engineer
1 a 1 2
2 b 1 3

表B

id real_name
1 jack
2 mike
3 joe

如果我要生成一张如下的表，请问sql语句是什么？
id product pm engineer
1 a jack mike
2 b jack joe

[CrazyFor]

select a.*,b.real_name as engineer from a,b where a.engineer=b.id

[arclala]

楼上的不对，pm列还是id不是real name

[jimmyxing]

做个视图，将它的外键连接，不就什么都出来了吗？

[twb88]

select a.id,a.product,b.real_name as pm,c.real_name as engineer
from a inner join b inner join c
a.id=b.id=c.id
试一试

[twb88]

select a.id,a.product,b.real_name as pm,c.real_name as engineer
from a inner join b inner join b as c
a.id=b.id=c.id

[CrazyFor]

select a.*,c.real_name,b.real_name as engineer from a,b,b as c where a.engineer=b.id and a.pm=c.id

[arclala]

谢谢CrazyFor(蚂蚁)
如果表A是这样，
表A

id product pm engineer tester
1 a 1 2 3
2 b 1 3 2

为了得到如下表，我还要重复的连接b吗？
id product pm engineer tester
1 a jack mike joe
2 b jack joe mike

select a.*,c.real_name,b.real_name as engineer,d.real_name as tester from a,b,b as c, b as d where a.engineer=b.id and a.pm=c.id and a.tester=d.id

有没有别的方法？谢谢！

[arclala]

up

[CrazyFor]

是的，还要加个B表，并给出别名。：）

[HawaiiLeo]

select a.id, a.product, b.real_name as pm, c.real_name as engineer,
d.real_name as tester from a, b, b as c, b as d where a.pm = b.id
and a.engineer = c.id and a.tester = d.id