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

Why do GROUP BY when no aggregate functions are used? Why not a simple SELECT DISTINCT instead? — jarlh, Nov 14 '15 at 10:06

score 0 · Accepted Answer · answered Nov 14 '15 at 09:09

0

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;

answered Nov 14 '15 at 09:09

Mehdi Haghshenas

2,433
1
16
35

Why do GROUP BY when no aggregate functions are used? Why not a simple SELECT DISTINCT instead? – jarlh Nov 14 '15 at 10:06
I think question query has more column that their contain aggregate function – Mehdi Haghshenas Dec 04 '15 at 19:55

SQL Server- Select Into another Select Got Error

1 Answers1