I would like to reproduce a process that cannot be reproduced by Aggregate using SQL.
I want to implement this in a single SQL due to performance issues.
What we would like to achieve↓
table1
------------------------
code | name
001 | name1
002 | name2
003 | changed
004 | name4
table2
003 | name3
This is the SQL I want to implement.
----------------------------------------------------------------
select code,name from {table1}
minus
select code,name from {table2}
result
Hi Ryowei,
You can achieve that with an aggregate, using the With or Without relationship type between both entities:
And filtering for the ones that there are not in Entity2:
This query will get you back the records in Entity1 which are not in Entity2.
In case you really want to use Advanced SQL, I would recommend using the NOT EXISTS, something like:
SELECT
{table1}.[Name],
{table1}.[Code]
FROM
{table1}
WHERE
NOT EXISTS (
1
{table2}
{table1}.[Code] = {table2}.[Code]
)
Kind Regards,João
Thank you,João
This is exactly what I wanted to know.