Hi Loi Hua,

Please see the screen shot for reference. just below the entity join you will see an expression , You can add additional logic there .

Basicly your ON statement is acting like an additional WHERE statement.

However, there can* be a slight difference in putting it in your ON or WHERE clause.

When using it as inside the WHERE, the joined row must match the WHERE statement, simular to a INNER JOIN.

Now when using a LEFT JOIN, even if the row does not match, it will simply not return a record from this table.

For your aggregate, I haven't tried this myself though, but you could try to use the A entity here, even though it doesn't display it in the window, you might be able to use it (I know for sure you can use variables in here for checking)

@Pranav Pandey

Please take a look at my screenshot. Since the JOIN is between 2 tables, I can't reference the 3rd one on the Expression Editor.

@Joey Moree

Yes, it is just as you said. And that is why I can't put it in the WHERE condition. And I tried to do that in Aggregate as the 3rd LEFT JOIN between B table and C table (top screenshot).

Hey Loi,

It seems you have to use an improvised WHERE statement (in the filter section of the aggregate)
Since you wish to gain the same result as with a LEFT JOIN you will have to use something like:

B.SECTION_CD = C.SECTION_CD OR C.SECTION_CD = NULLIDENTIFIER()

@Pranav Pandey, Joey Moree

I think improvised WHERE statement you both advised has just become the 3rd JOIN of B and C on my 1st screen shot. Am I wrong? (I can't say for sure. Sr!)

Maybe I should just go with Advanced Query for these kind of queries?

I guess I will go with Advanced Query for these kind of queries. Thank you both! :)

People...
This is very simple of achieving with an Aggregate.

This:

SELECT
    A.COMPANY_CD,
    B.COMPANY_INFO,
    C.SOMETHING
FROM A
    INNER JOIN B
    ON  A.COMPANY_CD = B.COMPANY_CD

    LEFT JOIN C
    ON  A.COMPANY_CD = C.COMPANY_CD
    AND B.SECTION_CD = C.SECTION_CD
WHERE
    SOME CONDITIONs

If A.COMPANY_CD must be equal to B.COMPANY_CD and A.COMPANY_CD must be equal to C.COMPANY_CD, we are saying the C.COMPANY_CD must be equal to B.COMPANY_CD.

B.COMPANY_CD = A.COMPANY_CD = C.COMPANY_CD

So, the above is exactly the same thing as this:

SELECT
    A.COMPANY_CD,
    B.COMPANY_INFO,
    C.SOMETHING
FROM A
    INNER JOIN B
    ON  A.COMPANY_CD = B.COMPANY_CD

    LEFT JOIN C
    ON  B.COMPANY_CD = C.COMPANY_CD
    AND B.SECTION_CD = C.SECTION_CD
WHERE
    SOME CONDITIONs

And the above we can do in an Aggregate.

Cheers.

I honestly thought this was an example to display a possibility to join on multiple entities, so I didn't pay much attention to the actual fields... 

It would still be possible to do with aggregate, but not as easy as with an SQL (requiring, probably, the use of calculated fields and other techniques).

But them, a better example would have to be given, as each case is a case :)

Hey Eduardo Jauch, Joey Moree,

Actually, the sample code is just for the purpose of looking at the possibility to join on multiple entities of the Aggregate.

I'm gonna give a better example that closely related to the thing I've been working on.

SELECT
    A.*, //example purpose
    B.*, //example purpose
    C.*  //example purpose
FROM A
    INNER JOIN B
    ON  B.SECTION_CD = A.SECTION_CD

    LEFT JOIN C
    ON  C.COMPANY_CD = A.COMPANY_CD
    AND C.DEP_CD     = B.DEP_CD
    AND C.CST_CD     = A.CST_CD
    
    LEFT JOIN D
    ON  D.COMPANY_CD = A.COMPANY_CD
    AND D.DEP_CD     = B.DEP_CD
    AND D.CST_CD     = A.CST_CD
    AND D.OTHER1     = A.OTHER1
    AND D.OTHER2     = A.OTHER2
    AND D.TRH_CD     = C.TRH_CD
WHERE
    SOME CONDITIONs

Is this still achievable with Aggregate?