SQL Query using STRING_AGG giving duplicates when joining 3 table

user_4995557 0
Viewed 70

I'm trying to join 3 tables together (Event, Symbol, Category). This is the basic design

Event Table:

Id | Title
-----------
 1 | Testing
 3 | Debugging

Symbol Table:

Event_Id | Symbol
-----------------
    1    |  TT
    1    |  UU

Category Table:

Event_Id |  Category
--------------------
    1    |   XV
    1    |   XX
    3    |   XL

The Join query I have written is:

SELECT event.Id , STRING_AGG(symbol.symbol, ',') symbol, STRING_AGG(category.category, ',') as category 
FROM Event as event 
    LEFT JOIN Category category ON event.publisherID = category.Id
    LEFT JOIN Symbol symbol on event.publisherID = symbol.Id
GROUP BY event.Id

The results I get is:

Id |     symbol    |  category
---------------------------------
 1 |  TT,UU,TT,UU  | XV,XV,XX,XX
 3 |     NULL      |    XL

How do I make it so that there wont be duplicates for the symbol and category columns?

sql-server sql
1 Answers

This is a case where OUTER APPLY is a big help:

SELECT e.Id , s.symbols, c.categories 
FROM Event e OUTER APPLY
     (SELECT STRING_AGG(c.category, ',') as categories
      FROM Category c
      WHERE e.publisherID = c.Id
     ) c OUTER APPLY
     (SELECT STRING_AGG(s.symbol, ',') as symbols
      FROM Symbol s
      WHERE e.publisherID = s.Id
     ) s;

By avoiding the outer aggregation, this should also be faster. I should note that the above is very similar to correlated subqueries:

SELECT e.Id , s.symbols, c.categories 
       (SELECT STRING_AGG(c.category, ',')
        FROM Category c
        WHERE e.publisherID = c.Id
       ) as categories,
       (SELECT STRING_AGG(s.symbol, ',')
        FROM Symbol s
        WHERE e.publisherID = s.Id
       ) as symbols
FROM Event e;
user_11440350
Related