AppGini Community Forums

I need help optimize the sql
I'm in a custom page,
I'm getting this array of result from a sql call
$res = sql("SELECT tipologia_intervento, COUNT(*) FROM dt_reg_interventi GROUP BY tipologia_intervento", $eo);

tiplogia_intervento is a foreign key

so, then I need to make a for lop of the array result since I have a foreign key here
$fk_intervento = $rows[$x][0];
$intercetta_label = sqlValue("SELECT descrizione_tip FROM dt_tip_intervento where id_tip_intervento='{$fk_intervento}'");

this work, it's also fast, and for how is made the request I don't have many rows, since I simply count the tiplogy, I coudl have max 10 rows
nevertheless I suspect I can get directly the foreign key writing a left join sql query inside the first request

only, I dont' know how to write it correctly in case you need to add count function

I am not sure, bu I think you mean something like this

select count(*), max(master_name) from detail_table
left JOIN master_table on master_id = master_detail_id
group by master_detail_id

the name of the master-table and a count of the detail-table.

thanks, I was able do to it,
you need to select also the foreign key field using the structure "table.field" in your select, before the join declaratioon

sql worked, but I didn't see the value, bacuase you need to write it in the select or ou simply continue to get the foreign key

now, I have to do a sql where i find two different foreign keys, so I try to write it, it's only question get the right way to write it,
I'm trying these things directly in mysql, there is a sql button where you can write sql and test if is correct,
appgini in any case return a red message when is wrong, but inside mysql is simple test a sql sentence

it's a language not complicated, but you need to be precise, it's no very flexible

the problem was i didn't get a error, I got a result is not what i needed, but there is a logic, now I start to get how it work