SQL Server子查询在选择两个表时返回多于1个值

Question

我试图从两个表中获得基于ID的名称的结果

但是我这样做，我得到以下消息:SQL Server子查询返回超过1个值

这里有两个表

TIME_TABLE
ACTIVITY_ID     ID_TIME     CREATED_BY_ID       MODIFIED_BY_ID
XF34            145         US1                 US5
XF56            146         US4                 US2
XF65            147         US4                 US5
XF76            148         US3                 US3

USER_TABLE
ID_USER     NAME
US1         JOHN        
US2         ALEX
US3         NOE
US4         GREG
US5         FRED

这就是我想要得到的结果。

RESULT
ACTIVITY_ID     ID_TIME     CREATED_BY_ID       MODIFIED_BY_ID
XF34            145         JOHN                FRED
XF56            146         GREG                ALEX
XF65            147         GREG                FRED
XF76            148         NOE                 NOE

Here my Query

SELECT
ACTIVITY_ID,
ID_TIME,
(SELECT T1.NAME FROM USER_TABLE T1 LEFT JOIN TIME_TABLE T2 ON T1.ID_USER = T2.CREATED_BY_ID) AS CREATED_BY,
(SELECT T1.NAME FROM USER_TABLE T1 LEFT JOIN TIME_TABLE T2 ON T1.ID_USER = T2.MODIFIED_BY_ID) AS MODIFIED BY
FROM TIME_TABLE

我找不到我的请求哪里错了。

你能帮我一下吗?

Answer 1

OK找到解决方案了。我需要把连接后，而不是在子查询。这样的

SELECT
T1.ACTIVITY_ID,
T1.ID_TIME,
T2.NAME AS CREATED_BY,
T3.NAME AS MODIFIED_BY
FROM TIME_TABLE T1
LEFT JOIN USER_TABLE T2 ON T2.ID_USER = T1.CREATED_BY_ID
LEFT JOIN USER_TABLE T3 ON T3.ID_USER = T1.MODIFIED_BY_ID