SQL : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I tried query like this but i got message like

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Code:

select
    MaterialID = REPLACE(MaterialID, 'MA','Material'),
    MaterialName = UPPER(MaterialName),
    (select
         PurchaseDate,
         PurchaseQty
     from
         MsPurchase
     where 
         PurchaseQty > AVG(PurchaseQty))
from
    MsMaterial
where
    MaterialType = 'supplies'

Answer

I guess you need below –

select
    m1.MaterialID = REPLACE(MaterialID, 'MA','Material'),
    m1.MaterialName = UPPER(MaterialName),
    m2.PurchaseDate,
    m2.PurchaseQty
from
    MsMaterial m1
join (select
        PurchaseDate,
        PurchaseQty,
        MaterialID
     from
         MsPurchase
     where 
        PurchaseQty > AVG(PurchaseQty)
    ) m2 on m1.MaterialID = m2.MaterialID
where
    MaterialType = 'supplies'