此子查询最多可以返回一条记录. (错误3354) [英] At most one record can be returned by this subquery. (Error 3354)

问题描述

我的查询出现此错误，可以帮助我恢复它

Hi my query getting this error help me to recover it

SELECT CompanyId, CompanyName, RegistrationNumber,
  (select CompanyAddress from RPT_Company_Address where 
   RPT_Company_Address.CompanyId=Company.CompanyId) AS CompanyAddress, 
  MobileNumber, FaxNumber, CompanyEmail, CompanyWebsite, VatTinNumber
FROM Company;`

推荐答案

看来，您的RPT_Company_Address表具有给定公司的多个地址.如果这不可能，则应尝试更正数据并修改架构以防止发生这种情况.

It appears that your RPT_Company_Address table has more than one address for a given company. If this should not be possible, you should try to correct the data and modify your schema to prevent the possibility of this happening.

另一方面，如果可以有多个地址，则必须确定查询应如何处理它们:

On the other hand, if there can be multiple addresses, you must decide how your query should handle them:

1)您是否要多次列出同一公司行-每个地址一次?如果是这样，请使用INNER JOIN全部返回它们:

1) Do you want the same company row listed multiple times-- one per each address? If so, use an INNER JOIN to return them all:

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN RPT_Company_Address RCA ON RCA.CompanyId = Company.CompanyId

2)如果仅需要第一个匹配地址，请对每个公司对应的第一个匹配地址进行子查询:

2) If you want only the first matching address, do a subquery on the first matching address corresponding to each company:

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN
(
    SELECT CompanyId, ROW_NUMBER() OVER (ORDER BY 1 PARTITION BY CompanyId) AS Num
    FROM RPT_Company_Address
) Addresses
    ON Addresses.ComapnyId = Company.CompanyId  
WHERE Num = 1

3)如果您可以通过其他方法来标识所需的主要"地址，请在该条件中加入WHERE子句:

3) If you have some other way to identify the "primary" address that you want, include a WHERE clause with that criteria:

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN RPT_Company_Address RCA ON RCA.CompanyId = Company.CompanyId
WHERE RCA.PrimaryAddress = 1

这篇关于此子查询最多可以返回一条记录. (错误3354)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！