Return only distinct values from multiple SQL correlated sub-queries

This query partially completes. It returns 16 of 1,135 rows. The error message below indicates duplicates in the sub-queries.

I have tried inserting DISTINCT and IN syntax to no avail. Any help would be greatly appreciated.

SELECT a.AAAREFNUMVALUE AS "Pro Number",
   a.AAATRANSPORTTABLE AS "Table ID",
   a.AAAREFNUMTYPE AS "Number Type",
   a.AAAPROSUFFIX AS "Pro Suffix",
   d.AAADB2Date AS "Pickup Date",
   t.AAATOBILLINGTERMID as "Billing Terms",
   t.AAAOriginTerm as "Origin Terminal",
   t.AAADestTerm as "Destination Terminal",

(SELECT a2.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a2
        WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a2.AAAREFNUMTYPE = 2
        ) AS "Shippers BL#",

        (SELECT a3.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a3
        WHERE a3.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a3.AAAREFNUMTYPE = 3
        ) AS "PO #",

        (SELECT a4.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a4
        WHERE a4.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a4.AAAREFNUMTYPE = 8
        ) AS "SHIPPERS #"

FROM dbo.AAATOREFNUMS a 
INNER JOIN dbo.AAATODATES d ON a.AAATRANSPORTTABLE = d.AAATRANSPORTTABLE 
INNER JOIN dbo.AAATRANSPORTTABLE t ON d.AAATRANSPORTTABLE = t.RECID
WHERE AAAREFNUMTYPE = 1 ;

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,>, >= or when the subquery is used as an expression.

Answer

This error message doesn’t indicate duplicates on the correlate subqueries, but multiple values. So it raises an error because the engine can’t determine which of those values is the ones that should be returned.

You can add a TOP 1 on the subqueries to tell the engine that the first value of each subquery is the one to be returned.

SELECT a.AAAREFNUMVALUE AS "Pro Number",
   a.AAATRANSPORTTABLE AS "Table ID",
   a.AAAREFNUMTYPE AS "Number Type",
   a.AAAPROSUFFIX AS "Pro Suffix",
   d.AAADB2Date AS "Pickup Date",
   t.AAATOBILLINGTERMID as "Billing Terms",
   t.AAAOriginTerm as "Origin Terminal",
   t.AAADestTerm as "Destination Terminal",

(SELECT TOP 1 a2.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a2
        WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a2.AAAREFNUMTYPE = 2
        ) AS "Shippers BL#",

        (SELECT TOP 1 a3.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a3
        WHERE a3.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a3.AAAREFNUMTYPE = 3
        ) AS "PO #",

        (SELECT TOP 1 a4.AAAREFNUMVALUE 
        FROM dbo.AAATOREFNUMS a4
        WHERE a4.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND
                a4.AAAREFNUMTYPE = 8
        ) AS "SHIPPERS #"

FROM dbo.AAATOREFNUMS a 
INNER JOIN dbo.AAATODATES d ON a.AAATRANSPORTTABLE = d.AAATRANSPORTTABLE 
INNER JOIN dbo.AAATRANSPORTTABLE t ON d.AAATRANSPORTTABLE = t.RECID
WHERE AAAREFNUMTYPE = 1 ;

But this is not recommended, because you shouldn’t return a random value like that, you should check which subquery unexpectedly returns more than one value, and determine which is the correct of those multiple values, modifying that subquery so it returns that one (using an ORDER BY, for example).