Advanced Query

  

Imagine that i'm trying to use an advanced query to do the following:

- I have a table in which i want to capture the CreatedBy and LastUpdatedBy for a record

- In order to get the name of the person, I two time link the user table, first time to get the name of the CreatedBy and second time of the LastUpdatedBy.

When using simple query it is very simple to set up the query so it joins the same table twice (because it automatically numbers the tables (user and user_2), but I don't know how to do that in advanced query.

Can someone give me a hint?

Hello Jonh.

In the advanced query, you can take full advantage of all the SQL syntax, because it's really just SQL.

So in the advanced query, you can do exactly the same as you did in the aggregate since the aggregate is just an accelerator and generates SQL statements.  

You can even take a look at the executed SQL after testing an aggregate if you have any doubt, like this sample:


John Oele wrote:

Imagine that i'm trying to use an advanced query to do the following:

- I have a table in which i want to capture the CreatedBy and LastUpdatedBy for a record

- In order to get the name of the person, I two time link the user table, first time to get the name of the CreatedBy and second time of the LastUpdatedBy.

When using simple query it is very simple to set up the query so it joins the same table twice (because it automatically numbers the tables (user and user_2), but I don't know how to do that in advanced query.

Can someone give me a hint?

John,

You can do it like this:

SyntaxEditor Code Snippet

SELECT {User}.[Id]
     , {User}.[Username]
     , {User}.[Name]
     , {User}.[Email]
     , {User}.[MobilePhone]
     , {User}.[Last_Login]
     , {User}.[Is_Active]
     , Manager.[Id]
     , Manager.[Username]
     , Manager.[Name]
  FROM {User}
 LEFT JOIN {UserExtension} ON {UserExtension}.[UserId] = {User}.[Id]
 LEFT JOIN {User} AS Manager ON Manager.[Id] = {UserExtension}.[ManagerId]  
 WHERE {User}.[Is_Active] = 1


Hi John,

You need to write advance sql like below:

Select OU1.Name,OU2.name from Table1, Ossys_User OU1,Ossys_User OU2
where Table1.CREATED_BY=OU1.Id and Table1.LastUpdatedBy =OU2.id


Regards

-SK-