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

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.