Not able to apply join in sql query

Question

i want to add the join "JOIN {User} ON {Deck}.[CreatedBy] = {User}.[Id]" in following query. I tried many combination but not succeeded. i want to fetch Name from User table

    SELECT  @CampaignQueryFilterString AS [Selected],
    {Deck}.[Id], {Deck}.[Name],
    {User}.[Name],
    {Deck}.[TableOfContentId], {Deck}.[CreatedBy],
    {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate],s1.[Count]      
    FROM  {Deck}
    JOIN
    (
        SELECT {Deck}.[Id],
        LISTAGG( {TagValue}.[Id], ',' ) WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId] ) AS Tags
        FROM {Deck} 
        JOIN {AssociatedDeckTags}
        ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id]
        JOIN {TagValue}
        ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id]
        WHERE {Deck}.[IsPublished] = 1
                AND {Deck}.[IsActive] = 1 AND {Deck}.[ReplacedByDeckId] IS NULL
                AND {Deck}.[TableOfContentId] IN @TableOfContentIdFilterString
                AND {Deck}.[ContentFileTypeId] IN @AllowedContentType
                AND {Deck}.[ExpiryDate] > SYSDATE
        GROUP BY {Deck}.[Id]       
    ) DeckView
    ON {Deck}.[Id] = DeckView.Id  
    JOIN(
        SELECT COUNT(*) AS [Count], {DeckGroup}.[DeckId] AS [S1DeckId]
        FROM {DeckGroup}
        JOIN {Slide} ON {Slide}.[DeckGroupId] = {DeckGroup}.[Id]
        GROUP BY {DeckGroup}.[DeckId]
    ) s1 ON s1.[S1DeckId] = {Deck}.[Id]
    @RegexString
    @SearchFilter
    @CreatedBy
    GROUP BY 
    {Deck}.[Id], {Deck}.[Name], {Deck}.[TableOfContentId], 
    {Deck}.[CreatedOn], {Deck}.[CreatedBy],
    {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate], 
    {Deck}.[NumOfPreviews], {Deck}.[NumOfDownloads],s1.[Count]        
    ORDER BY @Orderby

You tagged it as "Oracle", but - that's not Oracle, as far as I can tell ... — Littlefoot, Dec 27 '17 at 07:16
Doesn't look like MySQL either. MS SQL Server uses `[` and `@` for identifiers but not sure about `{` — APC, Dec 27 '17 at 07:25
"@" are input parameter dynamically. {} refers to table name and [] refers to column name — lovish Goyal, Dec 27 '17 at 08:23
It's outsystems way to build a costum query. `@xxx` is an input variables. `{xxx}` are tables and `[xxx]` are columns — Mark Baijens, Jan 05 '18 at 14:22

score 1 · Answer 1 · answered Dec 27 '17 at 11:41

Looks like simply join, please try the below query (using your notation). I added left join and in group by clause - {User}.[Name]. Optionally you can use some aggregating function for {User}.[Name] - max(), listagg() and remove it from group by clause.

SELECT @CampaignQueryFilterString AS [Selected],
       {Deck}.[Id], {Deck}.[Name], {User}.[Name], {Deck}.[TableOfContentId], 
       {Deck}.[CreatedBy], {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate], s1.[Count]
  FROM {Deck}
  JOIN DeckView ON {Deck}.[Id] = DeckView.Id            -- subquery1 
  JOIN s1 ON s1.[S1DeckId] = {Deck}.[Id]                -- subquery2 
  LEFT JOIN {User} ON {Deck}.[CreatedBy] = {User}.[Id]  -- <-- add join here
  @RegexString
  @SearchFilter
  @CreatedBy
  GROUP BY 
    {Deck}.[Id], {Deck}.[Name], {User}.[Name],          -- <-- add column here 
    {Deck}.[TableOfContentId], {Deck}.[CreatedOn], 
    {Deck}.[CreatedBy], {Deck}.[LastModifiedOn], 
    {Deck}.[ExpiryDate], {Deck}.[NumOfPreviews], 
    {Deck}.[NumOfDownloads], s1.[Count]
  ORDER BY @Orderby

You didn't show your tries, so we don't know if there was an error or undesired result. With this form of question that is all I can help. Also USER is one of Oracle Reserved Words, it's better to avoid using it as alias, variable name etc.

Not able to apply join in sql query

1 Answers1