ms access sql with union all and inner join

Question

I am trying to write a sql with union all and a field with inner join [Code].

 SELECT TypeID, 
       (SELECT CodeID 
          FROM tblID
          INNER JOIN tblA ON tblID.TypeID = tblA.TypeID) as Code,        
    APrice as 1,         
    Null as 2 
 FROM tblA 
 UNION ALL 
 SELECT TypeID, 
       (SELECT CodeID 
          FROM tblID
          INNER JOIN tblM ON tblID.TypeID = tblM.TypeID) as Code,
    Null as 1,
    MPrice as 2          
 FROM tblM;

Answer 1

I'm just taking a guess here that your inner subquery is returning more than one record and it's getting all janked up. It's impossible to say though since you didn't share sample data or your error message.

Consider rewriting this without the subqueries using a LEFT OUTER JOIN (below) or switch to a proper correlated subquery.

SELECT TypeID
    ,tblID.CodeID
    ,APrice AS 1
    ,NULL AS 2
FROM tblA 
    LEFT OUTER JOIN tblID 
        ON tblID.TypeID = tblA.TypeID

UNION ALL

SELECT TypeID
    ,tblID.CodeID
    ,NULL AS 1
    ,MPrice AS 2
FROM tblM
    LEFT OUTER JOIN tblID 
        ON tblID.TypeID = tblM.TypeID

Answer 2

Your subqueries are returning more than one row. Presumably, you intend a correlated subquery. That would look like:

SELECT TypeID, 
       (SELECT CodeID FROM tblID WHERE tblID.TypeID = tblA.TypeID) as Code,        
       APrice as aprice,         
       Null as mprice
FROM tblA 
UNION ALL 
SELECT TypeID, 
       (SELECT CodeID FROM tblID WHERE tblID.TypeID = tblM.TypeID) as Code,
       Null as aprice,
       MPrice as mprice          
FROM tblM;

Notes:

• You would see this error if you ran either subquery individually. You should learn how to debug queries.
• I don't think numbers are valid column aliases. In any case, they are a bad choice, so give the columns real names.
• This logic would normally be handled with an inner join or left join.