Output2 - SQL [message #7334] |
Wed, 21 August 2019 17:11 ![Go to next message Go to next message](/theme/default/images/down.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
I have an output2 pattern and I want to list only records of the chosen value in field 'saison_id'. I searched the net to look for the right SQL solution but I failed. Can you say why my SQL code works with a 'hard' code (2018/2019) and not with the one (training_plan_kopf.saison_id) you see below?
Best regards,
Juergen
$table_id = 'training_plan_anwesenheit'; // table name
$report = 'training_plan_anwesenheit.gesamt.list.report.inc'; // file identifying report structure
// customise the SQL SELECT statement
$sql_select = "tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname, tplan_anwesenheit_status_grund, tplan_anwesenheit_saison_id, " .
"orga_vereine.vereine_name, orga_vereine.vereine_logo, orga_mannschaften_eigene.mannschaften_desc, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 1 THEN tplan_anwesenheit_status ELSE 0 END) AS anzahl_anwesenheit, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum > NOW() THEN 1 ELSE 0 END) AS anzahl_absagen_zukunft, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'URL' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_url, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'PRV' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_prv, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'BER' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ber, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'VLZ' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_vlz, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'KRH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_krh, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SO' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_so, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'TAM' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_tam, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SCH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_sch, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UNI' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_uni, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'NIK' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_nik, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UE' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ue";
$sql_from = 'training_plan_anwesenheit ' .
'LEFT JOIN orga_vereine ON (' .
'orga_vereine.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_vereine.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_vereine.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel) ' .
'LEFT JOIN orga_mannschaften_eigene ON (' .
'orga_mannschaften_eigene.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_mannschaften_eigene.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_mannschaften_eigene.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'orga_mannschaften_eigene.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'orga_mannschaften_eigene.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id) ' .
'LEFT JOIN training_plan_kopf ON (' .
'training_plan_kopf.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'training_plan_kopf.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'training_plan_kopf.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'training_plan_kopf.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'training_plan_kopf.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id AND '.
'training_plan_kopf.training_plan_kopf_no = training_plan_anwesenheit.tplan_anwesenheit_kopf_no)';
$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = training_plan_kopf.saison_id";
-> this code doesn't work.
$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = '2018/2019'";
-> this code works. Only records with value 2018/2019 are shown
$sql_groupby = 'tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname';
$sql_having = null;
$sql_orderby = null;
require 'std.output2.inc'; // activate page controller
|
|
|
Re: Output2 - SQL [message #7335 is a reply to message #7334] |
Thu, 22 August 2019 04:07 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Your query is basically SELECT ... FROM training_plan_anwesenheit LEFT JOIN training_plan_kopf ...
WHERE training_plan_anwesenheit.tplan_anwesenheit_saison_id = 2018/2019'
If you replace the literal '2018/2019' with the column name training_plan_kopf.saison_id you are basically saying "use whatever value you find in column training_plan_kopf.saison_id", and as there is nothing in that query that limits that value to '2018/2019' it will use whatever values it finds.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Output2 - SQL [message #7351 is a reply to message #7350] |
Sat, 31 August 2019 04:41 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
When you have the OUTPUT2 (child) task as a navigation button on another (parent) task and you press that navigation button there are two values which are passed to the child task - the original WHERE string of the parent, and a SELECTION string which identifies any rows which you checked using the select box on the parent's screen. The field names used in both strings are the names as known to the parent. If the corresponding names in the child task are different then you have to adjust them manually as described in http://www.tonymarston.net/php-mysql/menuguide/appendixi.htm l
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|