SQL Server 2014 STUFF with 2 Inner Join

Question

My question really similar to this Use STUFF with INNER JOIN Query, but I've been trying what if the first column is a varchar type, currently this is my table. As for note I'm using SQL Server 2014

Table Customer

Cust_ID | Cust_Name        
--------+---------
 1      | Name1
 2      | Name2

Table Order

Order_ID | Order_NO | Cust_ID
---------+----------+---------
 1       | No.001   |   1
 2       | No.002   |   2

Table Item

 Item_ID | Order_ID | Item_Name | Quantity | Price         
 --------+----------+-----------+----------+-------
 1       |   1      |   A       |    1     | 10
 2       |   1      |   B       |    1     | 20
 3       |   1      |   C       |    1     | 30
 4       |   2      |   D       |    1     | 40

A few queries that I've tried:

SELECT 
    TBL_Sales_SO.SO_NO AS int, Tbl_Customer.Customer_Name, 
    ITEMS = STUFF ((SELECT DISTINCT ',' + TBL_SO_LIITEM.Item_Name
                    FROM Tbl_Customer
                    INNER JOIN TBL_Sales_SO ON Tbl_Customer.Com_Customer_ID =  TBL_Sales_SO.Com_Customer_ID
                    INNER JOIN TBL_SO_LIITEM ON TBL_Sales_SO.Sales_SO_ID = TBL_SO_LIITEM.Sales_So_ID
                    WHERE TBL_Sales_SO.SO_NO = TBL_Sales_SO.Sales_SO_ID
                    FOR XML PATH('')), 1, 1,'')
FROM 
    TBL_Sales_SO, Tbl_Customer
ORDER BY 
    SO_NO

and the query that for me I think almost hit

SELECT 
    TBL_Sales_SO.SO_NO, Tbl_Customer.Customer_Name, TBL_SO_LIITEM.Item_Name,
    TBL_SO_LIITEM.Quantity, TBL_SO_LIITEM.Price
FROM 
    ((Tbl_Customer
INNER JOIN 
    TBL_Sales_SO on Tbl_Customer.Com_Customer_ID = TBL_Sales_SO.Com_Customer_ID)
INNER JOIN 
    TBL_SO_LIITEM on TBL_Sales_SO.Com_Customer_ID = TBL_SO_LIITEM.Sales_So_ID)

While tinkering the first code give me some various error, the second one im just not sure how make possible something like my target

Order_No |   Name      | Item_Name | Quantity | Price         
 no.001  |   Name1     |   A,B,C   |    3     | 60
 no.002  |   Name2     |   D       |    1     | 40

UPDATE Following Gordon Answer, i tinkering the code again, manage to the following table

Order_No|   Name      | Item_Name         
no.001  |   Name1     |   A,B,C   
no.001  |   Name2     |   D
no.002  |   Name2     |   D 
no.002  |   Name1     |   A,B,C

and the query I use:

SELECT 
    s.SO_NO AS int, c.Cust_Name, 
    ITEMS = STUFF((SELECT DISTINCT ','+ Item_Name
                   FROM TBL_SO_LIITEM item
                   INNER JOIN TBL_Sales_SO s ON s.Sales_SO_ID = item.Sales_So_ID
                   WHERE c.Cust_ID = s.Cust_ID
                   FOR XML PATH('')), 1, 1, '')
FROM
    TBL_Sales_SO s, Tbl_Customer c
WHERE 
    c.Cust_Name IN ('Name1','Name2') 
ORDER BY 
    SO_NO

Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/q/18870326/2029983), [string_agg for sql server pre 2017](https://stackoverflow.com/q/49361088/2029983) — Thom A, Nov 20 '20 at 09:49
thx for the suggestion, after try the suggestion, it still doesnt work, probably because only 1 table, when i combine it with my data, it doesnt work, it shows repeating value, the first suggest repeat custname while the second one repating my item values instead adding it — yosua cahyadi, Nov 20 '20 at 11:10
EVOLVE! No one should be using [old style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). That is likely one problem why things "don't work" since you cross join in the outermost query. Worse is your combination of using both join styles. Inconsistent coding style is a bad habit. — SMor, Nov 20 '20 at 12:34
thank you for the suggestion, i've also been scolded about my messy code.. any pointer to learn about this @SMor — yosua cahyadi, Nov 20 '20 at 14:08

Gordon Linoff · Accepted Answer · 2020-11-21T15:09:53.840

1

You seem to want:

SELECT s.SO_NO AS int, c.Customer_Name, 
       STUFF( (SELECT DISTINCT ',' + oi.Item_Name
               FROM TBL_SO_LIITEM oi
               WHERE s.Sales_SO_ID = oi.Sales_So_ID
               FOR XML PATH('')), 1, 1,''
            ) as items
FROM TBL_Sales_SO s JOIN
     Tbl_Customer c
     ON s.cust_id = c.cust_id
ORDER BY SO_NO;

Note that the tables are only included once, either in the subquery or in the outer query. The naming conventions for your data is quite inconsistent, so the above is my best guess.

edited Nov 21 '20 at 15:09

answered Nov 20 '20 at 12:55

Gordon Linoff

1,242,037
58
646
786

thx for the insight, ive also edited the question a little bit neatly i think, just need to clue for the kinda duplicates one – yosua cahyadi Nov 20 '20 at 16:41
@yosuacahyadi . . . Run the code in my answer. Not the broken code using `,` in the `FROM` clause. – Gordon Linoff Nov 20 '20 at 16:58
Ah yes, the from clause works, while the the stuff clause missing some arguments, i will added the full code in my Question, again.. thank you for the insight – yosua cahyadi Nov 21 '20 at 04:38

SQL Server 2014 STUFF with 2 Inner Join

1 Answers1