SQL Server关联两张表查询时子查询返回多个值错误如何解决

错误原因 #

你编写的SELECT列表中的子查询没有和外层主查询的TIME_TABLE当前行建立关联条件，导致每个子查询执行时都会返回全表匹配后的多条姓名结果，触发Subquery returned more than 1 value报错。

正确查询写法 #

推荐直接关联两次USER_TABLE的写法，性能和可读性更好：

SELECT
  t.ACTIVITY_ID,
  t.ID_TIME,
  u1.NAME AS CREATED_BY_ID,
  u2.NAME AS MODIFIED_BY_ID
FROM TIME_TABLE t
LEFT JOIN USER_TABLE u1 ON t.CREATED_BY_ID = u1.ID_USER
LEFT JOIN USER_TABLE u2 ON t.MODIFIED_BY_ID = u2.ID_USER

如果需要使用子查询的写法，补充和外层行的关联条件即可：

SELECT
  ACTIVITY_ID,
  ID_TIME,
  (SELECT NAME FROM USER_TABLE WHERE ID_USER = t.CREATED_BY_ID) AS CREATED_BY_ID,
  (SELECT NAME FROM USER_TABLE WHERE ID_USER = t.MODIFIED_BY_ID) AS MODIFIED_BY_ID
FROM TIME_TABLE t

内容的提问来源于stack exchange，提问作者Grundulo