14
Views
3
Comments
Advanced SQL: joining to the same entity more than once with multiple differ entities
Question

Hi,

When using advanced SQL queries, is it possible to join the same table more than once with different entities? i tried to write the query as below where CreatedUser and AssignedUser  both are renames User Table,but i am getting the error like "CreatedUser is an unknown entity" ,"AssignedUser is an unknown entity".

 


Thanks In advance

Rank: #129

Hi,


It is possible, yes.

You have to create an alias for a table, see the example below where I give the alias u for the second user table:


Be aware, that you will lose the autocomplete functionality for the fields on tables with alias and it become more error-prone.


Hope it helps.


Cheers,

João

mvp_badge
MVP
Rank: #134

Hello, bhagya,

You can follow João's post and it will work. In your case, you are using {} on join condition and you don't need it. You need to remove it and simply use the alias CreatedUser and AssignedUser.


BR,

Luis

Rank: #129

Luís is right and besides, there are more points to fix:

  • you have 3 tables on select but your output structure has 4 tables, they also need to match. So you should add the OR_TP_TerminationAssignment on the Output Entities:

  • you are using the alias with {} and you should remove them (like my example and what Luís said)
  • you are not joining with table OR_TP_TerminationAssignment.

Assuming your connection OR_TP_TerminationAssignment joins with table OR_TP_Terminiation via OR_TP_TerminationId, your query should look like this:


SELECT CreatedUser.*, AssignedUser.*, {OR_TP_Termination}.*, {OR_TP_TerminationAssignment}.* FROM {OR_TP_Termination}
INNER JOIN {User} as CreatedUser ON CreatedUser.[Id] = {OR_TP_Termination}.[CreatedBy]
INNER JOIN {OR_TP_TerminationAssignment} ON {OR_TP_Termination}.[Id] = {OR_TP_TerminationAssignment}.[OR_TP_TerminiationId]
INNER JOIN as AssignedUser ON AssignedUser.[Id] = {OR_TP_TerminationAssignment}.[AssignedTo]


Hope it helps.


Cheers,

João