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
SQL JOIN HELP
Re: SQL JOIN HELP
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.
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.
Bert
I am using Appgini 5.75
I am using Appgini 5.75
Re: SQL JOIN HELP
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
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