At most one record can be returned by this subquery. (Error 3354)

ms-access, sql

Solution

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) 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) 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) 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

Problem

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;` ```

Original source