Kirk Sealls
Rank: #18316
126Views
7Comments
Solved
Advanced SQL: joining to the same entity more than once?
Question

When using advanced SQL queries, is it possible to join to the same table more than once? Normally in SQL I'd just use an "as" to alias the table differently for each instance, but OutSystems doesn't seem to like the "as" keyword.

For example, if you've got a static entity with simple "Yes / No / NA" options, and an entity with several different columns all joining to that table, but you can't use an aggregate because the filtering criteria for the data is too complicated.

0
0
on 2020-02-14
Copy link to comment
James Andrade
Rank: #613
Solution

Yes, as Daniel already said.

I will complement showing these two examples:

Using Aggregate, the Joins is allways to the same static Entity.


Using AQ:


Regards,


James


See solution in context
1
0
on 2020-02-15
Copy link to comment
Daniël Kuhlmann
mvp_badge
MVP
Rank: #17

Hi Kirk,

Yes you can use aliases, the AS keyword is not needed.

Regards,

Daniel

2
0
on 2020-02-14
Copy link to comment
James Andrade
Rank: #613
Solution

Yes, as Daniel already said.

I will complement showing these two examples:

Using Aggregate, the Joins is allways to the same static Entity.


Using AQ:


Regards,


James


1
0
on 2020-02-14
Copy link to comment
JitendraYadav
mvp_badge
MVP
Rank: #133

Kirk Sealls wrote:

When using advanced SQL queries, is it possible to join to the same table more than once? Normally in SQL I'd just use an "as" to alias the table differently for each instance, but OutSystems doesn't seem to like the "as" keyword.

For example, if you've got a static entity with simple "Yes / No / NA" options, and an entity with several different columns all joining to that table, but you can't use an aggregate because the filtering criteria for the data is too complicated.


Hi,

The main objective of Advanced SQL is to give us the same platform as the SQL Does. 


Thanks

0
0
on 2020-02-15
Copy link to comment
Jitender Gaur
Rank: #885

Hi Kirk,


In Advance SQL Option you can do same as MSSQL database like you have to use join, Union and other operation. You just need to set Output parameter same as SQL query will return. 


For Join with same table in Aggregate you can as do the same as "James" suggest.  


    

0
0
on 2020-02-15
Copy link to comment
Mohamed
Rank: #974

Dears,

Alias is a  quick solution, how ever considering the performance and readability, i will suggest to use the Common table expressions (With CTE)  from SQL server.


Please follow the below links to get more information on the With CTE. 


https://www.essentialsql.com/introduction-common-table-expressions-ctes/


https://www.mssqltips.com/sqlservertip/5118/sql-server-cte-vs-temp-table-vs-table-variable-performance-test/


Thanks

Hakkim.




0
0
on 2020-02-15
Copy link to comment
Kirk Sealls
Rank: #18316

Thanks all, marking James's reply as the solution, as his example showed me where I was syntactically incorrect (I was trying to put the alias name in brackets).

1
0
on 2020-02-21
Copy link to comment