SQL Server- Select Into another Select Got Error

Question

I create query to get my goods in my database as shown below

SELECT  
    Goods.GoodsID ,Max(Price)
    ExistNumber AS ExistNumber ,
    (SELECT p.ValueText
     FROM STR.GoodsProperties gp
     INNER JOIN PRP.Properties p ON p.PropertyID = gp.PropertyID
     WHERE NodeText LIKE '/40/%'
       AND gp.GoodsID = Goods.GoodsID) AS Color
FROM    
    STR.Goods
GROUP BY 
    Goods.GoodsID, ExistNumber;

This query get goods with their existnumber with color property of goods, now when I run this query I get this error:

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

This error caused because in some goods has two Color and I want get two rows for this goods

Answer 1

please check this query

SELECT  Goods.GoodsID ,
        ExistNumber AS ExistNumber ,
        Prop.ValueText AS Color
FROM    STR.Goods
        INNER JOIN ( SELECT gp.GoodsID ,
                            p.ValueText
                     FROM   STR.GoodsProperties gp
                            INNER JOIN PRP.Properties p ON p.PropertyID = gp.PropertyID
                     WHERE  NodeText LIKE '/40/%'
                   ) Prop ON Prop.GoodsID = Goods.GoodsID
GROUP BY Goods.GoodsID ,
        ExistNumber ,
        Prop.ValueText;