How to use STRING_AGG that selects from JOINs within an outer SELECT that also has JOINs

I have a compound SELECT statement that retrieves its values (columns) from several Joined tables. I want to add a column to the result set that is a STRING_AGG. The values that will be used in the STRING_AGG are retrieved from joined tables, including one of the tables that are in a JOIN already. If I run the STRING_AGG statement by itself, it returns exactly what I expect. I need to be able to combine the STRING_AGG with the existing compound SELECT statement, if possible.

The standalone STRING_AGG, which outputs a string with comma delimited values as expected, is as follows:

DECLARE @iIbrId INTEGER
SET @iIbrId = 1189  
SELECT STRING_AGG(CONVERT(NVARCHAR(max),sCdmName), ', ') WITHIN GROUP (ORDER BY sCdmName) AS CardMarks
    FROM (
    SELECT DISTINCT sCdmName 
    FROM mpm_icabinreq_ibr
    INNER JOIN mpm_icabinreq_cardmark_rcm ON iRcmIbrId = @iIbrId
    INNER JOIN mpm_cardmark_cdm ON iRcmCdmId = iCdmId) x

My SELECT statement that I wish to add the SELECT STRING_AGG to as a column in the result is as follows:

DECLARE @iIbrId INTEGER
SET @iIbrId = 631
SELECT a.iIbrId AS "Request Number", iIbsStatus, 
    sPerFirstName + ' ' + sPerLastName sPerFullName, a.iIbrRequest, a.iIbrTypId, sTypName,
    a.sIbrDesc, a.dIbrTarget, a.sIbrTransfer, a.iIbrBIN AS BIN, a.sIbrBINExtStart, a.sIbrBINExtEnd, a.iIbrEntIdClient,
    b.iIbrEntIdProcessor iPreviousProcessor
    FROM mpm_icabinreq_ibr a
    INNER JOIN mpm_ibrstatus_ibs ON a.iIbrId = iIbsIbrId
    INNER JOIN mpm_person_per ON iIbsPerId = iPerId
    INNER JOIN mpm_type_typ ON iTypId = a.iIbrTypId
    LEFT JOIN mpm_entity_ent ON iEntId = a.iIbrEntIdClient
    LEFT JOIN mpm_icabinreq_ibr b ON b.iIbrId = a.iIbrParentId
    LEFT JOIN mpm_multibank_mbk ON a.iMbkEntId = mpm_multibank_mbk.iMbkEntId
    LEFT JOIN mpm_bin_bin ON a.iIbrBin = iBinId
    LEFT JOIN mpm_icabinpseudort_irt ON iIrtIbrId = a.iIbrId

How can I add this SELECT STRING_AGG that uses joined tables to an existing SELECT statement that also uses one or more of the joined tables?

I want to add this column to an existing SELECT statement that uses one or more of the same tables in different joins. When I do this, I get NULL in all "CardMarks" columns.

This is the SQL statement that includes the SELECT STRING_AGG but that column returns NULL under this circumstance.

DECLARE @iIbrId INTEGER
SET @iIbrId = 631
SELECT a.iIbrId AS "Request Number", iIbsStatus, 
    sPerFirstName + ' ' + sPerLastName sPerFullName, a.iIbrRequest, a.iIbrTypId, sTypName,
    a.sIbrDesc, a.dIbrTarget, a.sIbrTransfer, a.iIbrBIN AS BIN, a.sIbrBINExtStart, a.sIbrBINExtEnd, a.iIbrEntIdClient,
    b.iIbrEntIdProcessor iPreviousProcessor,

    (SELECT STRING_AGG(CONVERT(NVARCHAR(max),sCdmName), ', ') WITHIN GROUP (ORDER BY sCdmName)  AS CardMarks
    FROM (
    SELECT DISTINCT sCdmName 
    FROM mpm_icabinreq_ibr
    INNER JOIN mpm_icabinreq_cardmark_rcm ON iRcmIbrId = @iIbrId
    INNER JOIN mpm_cardmark_cdm ON iRcmCdmId = iCdmId)x)

    FROM mpm_icabinreq_ibr a
    INNER JOIN mpm_ibrstatus_ibs ON a.iIbrId = iIbsIbrId
    INNER JOIN mpm_person_per ON iIbsPerId = iPerId
    INNER JOIN mpm_type_typ ON iTypId = a.iIbrTypId
    LEFT JOIN mpm_entity_ent ON iEntId = a.iIbrEntIdClient
    LEFT JOIN mpm_icabinreq_ibr b ON b.iIbrId = a.iIbrParentId
    LEFT JOIN mpm_multibank_mbk ON a.iMbkEntId = mpm_multibank_mbk.iMbkEntId
    LEFT JOIN mpm_bin_bin ON a.iIbrBin = iBinId
    LEFT JOIN mpm_icabinpseudort_irt ON iIrtIbrId = a.iIbrId

Solution

As I understand the naming convention for your table structure, mpm_icabinreq_cardmark_rcm is a junction table that defines a many-to-many relationship between mpm_icabinreq_ibr and mpm_cardmark_cdm. Given an iIbrId corresponding to an mpm_icabinreq_ibr row from your main query, you want a aggregate of all sCdmName values contained in mpm_cardmark_cdm.

My first question is which copy of mpm_cardmark_cdm in your main query are you interested in? This will drive whether you are looking up names based on a.iIbrId (child) or b.iIbrId (parent).

As for your existing subquery, there is no need to repeat the mpm_icabinreq_ibr table in your subquery. In fact, by doing so (and absent any further constraints on that table reference) I expect that your were seeing many duplicate names in the result, causing you to resort to DISTINCT.

Side note: Very often the use of DISTINCT, without knowing why, is a sign of an under-constrained query that needs a much closer look. Only use DISTINCT when you really understand why you need it. An example of a proper might be a customer who buys multiple products and sometimes buys the same product more than once. In that case, querying on distinct product (or writing a query that groups by product) would consolidate the duplicates.

Back to your query, I think that what you want is a select list item something like:

SELECT ...
    (
        SELECT
            STRING_AGG(CONVERT(NVARCHAR(max),sCdmName), ', ')
                WITHIN GROUP (ORDER BY sCdmName)
        FROM mpm_icabinreq_cardmark_rcm
        INNER JOIN mpm_cardmark_cdm ON iRcmCdmId = iCdmId
        WHERE iRcmIbrId = a.iIbrId  -- or perhaps b.iIbrId
    ) AS CardMarks
FROM mpm_icabinreq_ibr a
...

For row of your main query result, this will find and aggregate all of the sCdmName values matching the mpm_icabinreq_ibr record, using a.iIbrId and mpm_icabinreq_cardmark_rcm as a path to do the lookup.

An alternate syntax that helps unclutter your select list is to move the above subquery into a CROSS APPLY after your other joins, resulting in a query like:

SELECT ..., CM.CardMarks
FROM mpm_icabinreq_ibr a
...
CROSS APPLY (
    SELECT
        STRING_AGG(CONVERT(NVARCHAR(max),sCdmName), ', ')
            WITHIN GROUP (ORDER BY sCdmName) AS CardMarks
    FROM mpm_icabinreq_cardmark_rcm
    INNER JOIN mpm_cardmark_cdm ON iRcmCdmId = iCdmId
    WHERE iRcmIbrId = a.iIbrId  -- or perhaps b.iIbrId
) AS CM
...