how to use subquery?

Question

How to use subquery when return a multiple values in main query?

Example :

select ProductID from purchaseorderitemdetail where productid in

(select top 5 ProductID,SUM(Amount) from PurchaseOrderItemDetail group by ProductID order by SUM(amount) desc)

Thanking You

Hardik Bhavsar

Jaganathan Bantheswaran · Answer

Hi Sub query should not return multiple values. So you should use join here. select top 5 p.ProductID SUM(pod.Amount) from purchaseorderitemdetail p join PurchaseOrderItemDetail pod on pod.productid = p.ProductID group by pod.ProductID order by SUM(pod.amount) desc

Ahmar Husain · Answer

Dear Hardik After having a look on your query i would like to ask u a question you want to select records on the basis of product id (you are filtering the records on record id)  right ? then tell me why do you want SUM(Amount) in your subquery  select top 5 ProductIDSUM(Amount) from PurchaseOrderItemDetail....  . why dont u sum Amount in the main query .