I have 2 Entities like
1. Customer (ID, Name)

2. Order (ID, Date, CustomerID)

the data in columns are not mandatory except primary key.


I have a query that what join is suitable that the total records are always same for the result of aggregate if I change the order of both entity .

like Cutomer join with Order .. or Order join with Customer. the result must be same.

So what Join is most suitable for these.
Please help me like options are

1. Only With

2. With 

3. Both Only with and with 

4. with or without... 


Hi Manish,

Please follow the tutorials link it will helpful for you.


https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Handling_Data/Queries/Supported_Join_Types

Hope this will help you

Hi Manish,

Please check this document.

Hope that helps.

Manish Kumawat wrote:

I have 2 Entities like
1. Customer (ID, Name)

2. Order (ID, Date, CustomerID)

the data in columns are not mandatory except primary key.


I have a query that what join is suitable that the total records are always same for the result of aggregate if I change the order of both entity .

like Cutomer join with Order .. or Order join with Customer. the result must be same.

So what Join is most suitable for these.
Please help me like options are

1. Only With

2. With 

3. Both Only with and with 

4. with or without... 

Hi Manish Kumawat

   Only with , because it give intersection. It does not matter, how's your order of entity.

thanks 



Rahul Kumar wrote:

Manish Kumawat wrote:

I have 2 Entities like
1. Customer (ID, Name)

2. Order (ID, Date, CustomerID)

the data in columns are not mandatory except primary key.


I have a query that what join is suitable that the total records are always same for the result of aggregate if I change the order of both entity .

like Cutomer join with Order .. or Order join with Customer. the result must be same.

So what Join is most suitable for these.
Please help me like options are

1. Only With

2. With 

3. Both Only with and with 

4. with or without... 

Hi Manish Kumawat

   Only with , because it give intersection. It does not matter, how's your order of entity.

thanks 




Hi Rahul,
Why the second option is not correct.
I think if we join using with ... it also doesn't change the all records.

What you think about this 

Manish Kumawat wrote:

I have 2 Entities like
1. Customer (ID, Name)

2. Order (ID, Date, CustomerID)

the data in columns are not mandatory except primary key.


I have a query that what join is suitable that the total records are always same for the result of aggregate if I change the order of both entity .

like Cutomer join with Order .. or Order join with Customer. the result must be same.

So what Join is most suitable for these.
Please help me like options are

1. Only With

2. With 

3. Both Only with and with 

4. with or without... 



Can anyone know that which option is correct?

In which case the resulted records will be same if I change the order of entities for the join.

Solution

Hello Manish,

The "translation" of the Aggregate Joins to SQL Joins are:

With Or Without -> Left Join
Only With -> Inner Join
With -> Full Outer Join.

The one you need depends on your requirements. 

Can you have an Order WITHOUT a Customer or a Customer WITHOUT an Order?
If so, the only join that will give you the same results no matter the order of the entities in the join is the With (Full Outer Join).

Using the Only With will return only rows that match, so, the order of the entities will not matter, but if you have Customer without Orders, or Orders without Customers, those will not be present in the results.

With or Without, Left Join, will likely to give you different results depending on the order of the entities, as the left entity will have all its records, and the right will provide results only if there is a matching. So if you have customers without orders, they will appear in the results when the Customer entity is at the LEFT, but not when the Customer is at the right.

Hope this can help you.

Cheers.

Solution