这样的SQL语句怎么写，一个表有多个字段对应另一个表中的同一字段？

[mengbo]

如：User表(U)：UserID UserName
1 A
2 B

Info表(I)：InfoID StudentID TeacherID
100 1 2
其中StudentID、TeacherID都是存的UserID

要得到的结果是：I.InfoID I.StudentID U.UserName I.TeacherID U.UserName
100 1 A 2 B

当然要一个SQL语句搞定，谢谢！

[mengbo]

thanks，问题已经解决，给分

[sunshareforever]

select i.infoid,i.studentid,u.username,i.teacherid,uu.username
from info表 i left join user表 u on u.userid = i.studentid
left join user表 uu on uu.userid = i.teacherid

[txlicenhe]

Select a.InfoID,a.StudentID,b.UserName,a.TeacherID,c.UserName
from Info a
join User b on a.StudentID = b.UserID
join User c on a.TeacherID = c.UserID

[zjcxc]

select InfoID,StudentID
,UserName=(select top 1 username from user where studentid=a.studentid)
,TeacherID
,UserName1=(select top 1 username from user where TeacherID=a.TeacherID)
from Info a

[mengbo]

多谢！但如果再要关联其它表进行条件查询，会不会受影响
再者这样关联对效率的影响有多大

[pengdali]

select InfoID,StudentID,(select username from user where studentid=a.studentid) UserName,TeacherID,(select username from user where TeacherID=a.TeacherID) UserName1 from Info a

[gmlxf]

select InfoID,StudentID,(select UserName from User where UserIDa.StudentID) UserName1,TeacherID,(select UserName from user where UserID=a.TeacherID) UserName2 from Info a