求查询语句，子查询

[朱鹏程maprincesse]

我有2各表 结构如下
其中的report_by和update_by对应的都是U_ID，
如何通过一条查询语句把对应的用户名查出来，有人说用子查询，可是不会写，拜托

[grn0515]

select a.* ,b.U_Name from cap a,user b where a.report_by=b.uid and a.update_by=b.uid

[xdashewan]

select a.*, b1.U_NAME, b2.U_NAME 
from table1 a, table2 b1, table2 b2
where a.report_by = b1.U_ID
and a.UPDATE_BY = b2.U_ID

要出table1所有数据自己改下左联结

[insus]

try:

SELECT [CAP_ID],[CAP_NUM],[CAP_NAME],[report_date],[report_by],(SELECT [U_NAME] FROM [TABLE2] WHERE [U_id] = [report_by]) AS [ReportName],[UPDATE_BY],(SELECT [U_NAME] FROM [TABLE2] WHERE [U_id] = [UPDATE_BY]) AS [UpdateName] FROM [TABLE1]