So I have 2 tables in web SQL , one of them looks like this(there are thousands of rows):
customer_number | order_number
--------------------------------------------
1234 12
1234 13
1234 14
6793 20
6793 22
3210 53
etc.
And the other table like this(also thousands of rows):
customer_number | first_purchase_year
----------------------------------------------------
1234 2010
5313 2001
1632 2018
9853 2017
6793 2000
3210 2005
etc.
I have this code to select 10 customers from the first table and list all their purchases:
select top 10 * from
(select distinct t1.customer_number,
stuff((select '' + t2.order_number
from orders t2
where t1.customer_number = t2.customer_number
for xml path(''), type
).value('.','NVARCHAR(MAX)')
,1,0,'')DATA
from orders t1) a
Whch outputs this:
customer_number | order_number
--------------------------------------------
1234 12 13 14
6793 20 22
3210 53
What I need to do is ONLY display 10 random customers that have first_purchase_year > 2010. I am not sure how to check if first_purchase_year corresponding to a customer_number is greater than 2010. Thank you!
You just need to fix the subquery in the outer from clause:
select c.customer_number,
stuff((select '' + o2.order_number
from orders o2
where c.customer_number = o2.customer_number
for xml path(''), type
).value('.','NVARCHAR(MAX)'
), 1, 0, ''
) as data
from (select top (10) c.customer_number
from table2 c
where c.first_purchase_year > 2010
) c;