0

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'

BrettKB
  • 195
  • 1
  • 4
  • 21
  • `tblSku - Items field contains values 1,2 (in same field)` does this mean there is 1 record and that record has the value "1,2" in the `Items` field? If so, then there's your problem: `IN` doesn't recognize comma-separated string as a list of values; it's just a single string value. – Josh Part Nov 06 '15 at 22:21
  • Right. I've tried to change it so that 1,2 would read as '1','2' but I'm still getting NULL. – BrettKB Nov 06 '15 at 22:35
  • @BrettKB Replace doesnt work. Check my answer and see how you split the string into rows – Juan Carlos Oropeza Nov 06 '15 at 22:37

1 Answers1

1

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

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118