Hi all,
Why is there a second subquery in the sql script in the below screenshot? Both subqueries are using the same table in the FROM clause. Isn't it easier to just do:
SELECT DISTINCT MIN_CUSTOMER_KEY, CAST(PRODUCT_NUMBER AS VARCHAR) + '||' AS [text()]
FROM BLINDS_BI.[DBO].[PRODUCT_ACTIVITY_EMAIL] WITH (NOLOCK)
WHERE PRODUCT_ACTIVITY_TYPE ='FAVORITE'
You would have to ask JPE 
it is hard to do code reviews. And it is even harder to help you without sample data and the final result you want. Whoever wrote what you posted might have a reason they did it that way. But what are you trying to solve
Hahaha, unfortunately he's no longer with the company.
it is hard to do code reviews. And it is even harder to help you without sample data and the final result you want. Whoever wrote what you posted might have a reason they did it that way. But what are you trying to solve
I guess what I'm trying to understand if there is any logical reason to write this script with two subqueries. My understanding is that the purpose of the second subquery is to only select the PRODUCT_NUMBER column and use it in the SELECT clause of the first subquery. But my question is, can't that have been done with just one query, because both of the subqueries are referencing the same table.
Your query won't return the same results - but there are better ways of writing the original query. The reason for the 2 queries is because the sub-query is concatenating the results into a delimited string using FOR XML.
That can be replaced using STRING_AGG (2017 or higher).
Depending on the requirements - this could probably be rewritten as a single query without any derived tables or subqueries.
