here is my script. I want to finally get sum(pic_size) for every main_id in a single table (with other details as you see below but the main goal is that). Without INNER JOIN of sum and info tables i don't get an error, but it's not what I want which is a single table.
//get ids from main table
IDs:
LOAD id as main_id;
SQL SELECT id
FROM table1
ORDER BY id;
//get min and max id for loop
MinMaxID:
LOAD max(id) as MaxID,
min(id) as MinID
Resident IDs;
Let MaxID= Peek('MaxID', 0 , MaxID);
Let MinID = Peek('MinID', 0 , MinID);
Set a=$(MinID);
Do while a<= $(MaxID)
Info:
LOAD
id_fk as main_id,
id ,
pic_id,
pic_size;
SQL select t2.id , t2.id_fk ,
, pic.id as "pic_id", pic.id_fk
, file.id as "file_id", file.pic_fk,
file.file_size as "file_size"
from Table2 t2
inner JOIN Table3 pic
on t2.id=pic.id_fk
INNER JOIN Table4 file
on pic.id=file.pic_fk
where t2.id_fk='$(a)';
INNER JOIN(Info)
Sum:
LOAD
main_id,
sum(pic_size) as Total_File_Size
resident Info
group by main_id;
Let a=a+1;
Loop
this creates synthetic key like : main_id, id, pic_id.
I only want a single table so i tried inner join. How can i handle it?
