Use STUFF with INNER JOIN Query

Question

I have three tables in my database. A table for Product, a table for Types and a mapping table named Prod_Type. My database is sql server that's why I cant use the group_concat function and I am using the Stuff function.My table structures were as follows

ProductTable

Prod_ID    |   Name    |   Brand
-------        ----        -----
   1       |   Name1   |   Brand1
   2       |   Name2   |   Brand2
   3       |   Name3   |   Brand3
   4       |   Name4   |   Brand4
   5       |   Name5   |   Brand5
   6       |   Name6   |   Brand6
   7       |   Name7   |   Brand7

TypeTable

 Type_ID   |   TypeName
 -------       --------
    1      |   TypeName1
    2      |   TypeName2
    3      |   TypeName3
    4      |   TypeName4
    5      |   TypeName5

Prod_TypeTable

  Prod_IDM   |  Type_ID
  --------      -------
     1      |     1
     1      |     3
     1      |     4
     1      |     5
     2      |     2
     2      |     3
     3      |     4
     4      |     5
     4      |     1
     5      |     4
     5      |     3
     5      |     2
     6      |     2
     6      |     3
     7      |     5

I was able to join the table of product to the mapping table of Prod_type. I used stuff query to avoid multiple results. My query was something like this:

Select 
  top 5 * 
from  ProductTable 
   inner Join (SELECT  
                 Prod_IDM, 
                 STUFF((SELECT ', ' + CAST(Type_ID AS VARCHAR(10)) [text()]
                         FROM Prod_TypeTable 
                         WHERE Prod_IDM = t.Prod_IDM FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') TypeID
               FROM Prod_TypeTable t
               GROUP BY Prod_IDM   )  As TypeList on TypeList.Prod_IDM = ProductTable.Prod_ID

What I need to do now is to join my previous query result to type table in able to get the names of the types respectively. How can I possibly do that?My expected output would be like this

Prod_ID    |   Name    |  TypeName
-------        ----       ---------
   1       |   Name1   |  TypeName1,  TypeName3,  TypeName4,  TypeName5
   2       |   Name2   |  TypeName2,  TypeName3
   3       |   Name3   |  TypeName4
   4       |   Name4   |  TypeName5,  TypeName1
   5       |   Name5   |  TypeName4,  TypeName3,  TypeName2
   6       |   Name6   |  TypeName2,  TypeName3
   7       |   Name7   |  TypeName5

You should also show us what your expected output is here. – Tim Biegeleisen May 04 '18 at 01:47 — Tim Biegeleisen, May 04 '18 at 01:47
@TimBiegeleisen , I edited my question. Thanks – bleyk May 04 '18 at 01:52 — bleyk, May 04 '18 at 01:52

score 8 · Accepted Answer · answered May 04 '18 at 02:10

Group concatenation queries can be difficult to phrase in SQL Server, at least for earlier versions which do not have a STRING_AGG function. The trick is that the outer query should act on the table whose keys have values you want to aggregate from joining to one or more other tables. In this case, we put ProductTable on the outside, and then aggregate over everything else, to generate a CSV list of types for each product.

SELECT
    p.Prod_ID,
    p.Name,
    TypeName = STUFF((
        SELECT ',' + t.TypeName
        FROM Prod_TypeTable pt
        INNER JOIN TypeTable t
            ON pt.Type_ID = t.Type_ID
        WHERE pt.Prod_IDM = p.Prod_ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM ProductTable p
ORDER BY p.Prod_ID;

Demo

score 2 · Answer 2 · answered Jun 06 '18 at 08:25

If you are using SQL Server 2017+, I recommend using STRING_AGG as mentioned by @tim-biegeleisen. STRING_AGG concatenates the values of string expressions and places separator values between them (not added at the end of the string).

SELECT
      p.Prod_ID,
      p.Name,
      TypeName = (
      SELECT STRING_AGG ( t.TypeName, ',')
          FROM Prod_TypeTable pt
          INNER JOIN TypeTable t
              ON pt.Type_ID = t.Type_ID
          WHERE pt.Prod_IDM = p.Prod_ID
    )
FROM ProductTable p
ORDER BY p.Prod_ID;

To know more about concatenation of queries in SQL Server, I have written a blog at the link below. https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/

Use STUFF with INNER JOIN Query

2 Answers2

Demo

Linked