concatenation - SQL Server 2008 - field to concatenate strings -

i'm running following script, concatenated field returning incorrect values.

select customer_no, card_no, count(*) no_trans, stuff((select ',' + cast(trans_id varchar(20))      transactions (nolock)      a.customer_no = b.customer_no , a.card_no = b.card_no      xml path('')),1,1,'') trans_ids transactions b (nolock) date>= '01 apr 2013' , date < '30 apr 2013' , trans_id in (select trans_id           product_items (nolock)           product_item in ('298029')) group customer_no, card_no 

what i'm expecting no. trans (count(*)) have product_item in , return list of trans_id concatenated field.

eg.

customer_no            card_no        no_trans           trans_ids 1234                   12345          2                  1, 2 

but i'm getting is;

customer_no            card_no        no_trans           trans_ids 1234                   12345          2                  1, 2, 3, 5, 6 

can please tell me i've done wrong? in advance.

sample data

transactions table

customer_no         card_no        trans_id 1234                12345          1 1234                12345          2 

product items table

trans_id        product_item 1               298029 2               298029 

i guess issue need filter against product_items in for xml part of query well. using cte query rows want transactions , use cte concatenate trans_id.

here sql fiddle sample data shows believe issue , query using cte should want.

sql fiddle

ms sql server 2008 schema setup:

create table transactions (   customer_no int,   card_no int,   trans_id int )  create table product_items (   trans_id int,   product_item int )  insert transactions values (1234, 12345, 1), (1234, 12345, 2), (1234, 12345, 3), (1234, 12345, 4), (1234, 12345, 5)  insert product_items values (1, 298029), (2, 298029), (3, 298020), (4, 298020), (5, 298020) 

query 1:

-- query select customer_no, card_no, count(*) no_trans, stuff((select ',' + cast(trans_id varchar(20))      transactions (nolock)      a.customer_no = b.customer_no , a.card_no = b.card_no      xml path('')),1,1,'') trans_ids transactions b (nolock) trans_id in (select trans_id                    product_items (nolock)                    product_item in ('298029')) group customer_no, card_no 

results:

| customer_no | card_no | no_trans | trans_ids | ------------------------------------------------ |        1234 |   12345 |        2 | 1,2,3,4,5 | 

query 2:

-- rewritten use cte c (   select t.customer_no,           t.card_no,          t.trans_id   transactions t   t.trans_id in (select p.trans_id                        product_items p                        p.product_item in ('298029')) ) select c1.customer_no,        c1.card_no,        count(*) no_trans,        stuff((select ',' + cast(c2.trans_id varchar(20))               c c2                c1.card_no = c2.card_no ,                     c1.customer_no = c2.customer_no               xml path('')), 1, 1, '') trans_ids c c1 group c1.customer_no,           c1.card_no 

results:

| customer_no | card_no | no_trans | trans_ids | ------------------------------------------------ |        1234 |   12345 |        2 |       1,2 |