CODEWITHSUNDEEP
sqlsql-server
BrettKB
BrettKB

Reputation: 205

SQL SELECT Sum Subquery Returning NULL

I have a column that contains a concatenated list of items. Each of those components represents an id that I have from a different table (tblComponents). I am trying to use each of those items (using WHERE IN within my subquery) to find the qty of each component per item:

 SELECT s.title 'Product SKU'
 ,s.price 'Item Price' 
 ,CASE WHEN (select SUM(c.qty) from tblComponents where CID IN (s.Items)) = 0 
    THEN 1 
    ELSE c.qty 
 END 'Items Qty' 
 FROM tblsku s JOIN tblcomponent c ON c.idtblcomponent = s.idtblcomponent 
 JOIN tblgrade g ON g.idtblgrade = c.idtblgrade 
 WHERE --etc...

I've tried to separate the values within the WHERE IN using:

'''' + REPLACE(s.Items, ',', ''', ''') + ''''

It still returns a null value.

The SUM() is returning a null value.

Sample:

tblSku - Items field contains values 1,2 (in same field)

tblComponent CID 1 contains qty of 2, CID 2 contains qty of 3.

In this scenario I would like to display 5 as the SUM for field 'Items Qty'

Upvotes: 0

Views: 145

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Just because you have a string '1,2'

Doesnt mean those things are equal.

 CID IN ('1,2') <> CID IN (1,2)

You have to split the string into rows with something like this

Turning a Comma Separated string into individual rows

Upvotes: 1

Related Questions