Advanced SQL query Alias question

I have a a couple entities that I need to join in an advanced SQL query.


Cases has the following elements that I'm interested in.

CreatedBy  (Foreign key to user table)

LastUpdatedBy (Foreign key to user table)


I am struggling to figure out how the aliasing works... I really just need to join with the user table to grab the {User}.[Name]  for both the CreatedBy and LastUpdatedBy but everything I've tried fails when I run the query.    


SELECT {Cases}.[CreatedDt],
{Cases}.[LastUpdatedDt],
{Cases}.[Comment],

{Cases}.[CreatedBy],

{Cases}.[LastUpdatedBy],
User1.*,
User2.*


FROM {Cases}  

JOIN {User} User1 ON {Cases}.[CreatedBy] = User1.[Id]
JOIN {User} User2 ON {Cases}.[LastUpdatedBy] = User2.[Id]


(skipping the rest of the query because WHERE clauses and ORDERBY are very simple)


I'm sure I have a case of the Friday's and I'm overlooking something simple, can anyone assist?


Solution

Hi Josh,

Here is an example hope solve your issue.
Also, I'd like to ask isn't aggregate solve your need?

SyntaxEditor Code Snippet

SELECT DPT.Name, CreatedBy.Name, UpdatedBy.Name FROM {Department}  DPT
inner join {User} CreatedBy on CreatedBy.Id = DPT.CreatedBy
left join {User} UpdatedBy on UpdatedBy.Id = DPT.UpdatedBy



With best regards,
F.Karatay

Solution

Ferhat Karatay wrote:

Hi Josh,

Here is an example hope solve your issue.
Also, I'd like to ask isn't aggregate solve your need?

SyntaxEditor Code Snippet

SELECT DPT.Name, CreatedBy.Name, UpdatedBy.Name FROM {Department}  DPT
inner join {User} CreatedBy on CreatedBy.Id = DPT.CreatedBy
left join {User} UpdatedBy on UpdatedBy.Id = DPT.UpdatedBy



With best regards,
F.Karatay


I'm trying to implement the advanced filter builder forge module.. and in order to use that you have to create the SQL query manually.   Otherwise yes aggregates work great for this type of thing.   I'm going to study your structure here and see if I can get mine working.   Thanks

So when you alias the tables you don't have to place the attribute name in brackets []?   Normally I've always selected by indicating {entity}.[attribute]