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?
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
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?
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]