If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.

If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.

If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.

No, I don't think so. We have three tables, let's call them A, B and C. A and B have a 1:n relation, where C defines the type of B (it should've been a static entity, but this is old stuff, dating from pre-platform 5). What I want is a record A (identified by its Id), and its record B of a specific type C. So no B if there's no B of type C. In an advanced query, I'd do this:

SELECT {A}.*, {B}.*
FROM {A}
LEFT JOIN {B}
    INNER JOIN {C} ON {C}.[B_Id] = {B}.[Id]
        AND {B}.[Code] = 'X'
   ON {B}.[A_Id] = {A}.[Id]

So we have a nested inner join limiting B, which is left joined to C. I really can't see how I can do that in a single aggregate.

EDIT: left joined to A

The order of the joins in the Aggregate allows you to specify their priority/order by which they should be considered. So having:

1. B Only With C (C.B_Id = B.Id AND C.Code = "X")
2. A With or Without B (B.A_Id = A.Id)

Should generate a query similar to (adding parenthesis for clarity):

SELECT ...
FROM A LEFT JOIN (B INNER JOIN C ON C.B_Id AND C.Code = 'X') ON B.A_Id = A.Id

While swapping the order, would generate a query similar to:

SELECT ...
FROM (A LEFT JOIN B ON B.A_Id = A.Id) INNER JOIN C ON C.B_Id AND C.Code = 'X'

Hi Paulo,

I hadn't considered what you are writing, as I'm pretty sure that's not how simple queries were working (I don't think the order of conditions mattered much). I'll test it and report back...

Hi Kilian,

Did you have the chance to test it? Simple Queries already had that behaviour, but it only applied to outer joins (With or Without, Both). Inner joins (Only Withs) were always considered first, regardless of the order, so you couldn't have this particular configuration.

Hi Paulo,

I've tested it a bit, and it indeed seems to work this way. It's a pitty it doesn't seem to be documented anywhere, but at least we can have this idea as some form of documentation.

Still, I'd be happier if it was clearer in the interface that this kind of ordering is done.