topic Where clause in inner join not working in App Development

Where clause in inner join not working

Tenuki — Mon, 04 Aug 2025 13:50:06 GMT

Hello, I have 3 tables link to my products that I would like to inner join to have only data for the current year.

As I don't have any date in table1 and table2, I try to add a where clause on the last table, on the 'CreationDate' field.

But when I display the data, I have all my data, the condition is not working.
This is very confusing, because when I load the data, the number of lines has decreased.

My code look like this :
Table1:
ProductID
SupplierID;

Table2:
ProductID
ProductSalesCat1
ProductSalesCat2
INNER JOIN (Table1)
LOAD * RESIDENT Table2;

Table3:
ProductID
ProductName
CreationDate
YEAR(CreationDate) as YearCreationDate;
INNER JOIN (Table2)
LOAD * RESIDENT Table3
WHERE YEAR(CreationDate) = Year(Today());

I even try to drop Table1 and Table2 to clarify my scheme but it didn't help me.

Re: Where clause in inner join not working

anat — Mon, 04 Aug 2025 17:38:07 GMT

Table3:

load
ProductID
ProductName
CreationDate
YEAR(CreationDate) as YearCreationDate
WHERE YEAR(CreationDate) = Year(Today());

left join(Table3)

Table2:

load
ProductID
ProductSalesCat1
ProductSalesCat2;

left join(Table3)

Table1:

load
ProductID
SupplierID;

Re: Where clause in inner join not working

Tenuki — Tue, 05 Aug 2025 10:09:57 GMT

Many thanks Anat, I see that I didn't understand that order is important, and that I should start from the biggest table and merge all on it.