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?
There is Table4 where there's a field pic_size, but this table is very big and gets larger by day. So I want a script that goes from Table1 and main_id to Table4 and pic_size so that eventually i'll have main_id and sum(pic_size) for each of main_id, that stores every line that gets read in a csv file (or a table, haven't decided yet).
So i got the main_ids and did a loop in which i go from table1 to table4. But this ain't a solid table. Tried inner join between info and sum but as you see it's not working.
