[MySQL] una join complessa

[james]

Ho tre tabelle:

ALTEZZA (id, tipo)
id tipo
1 alto
2 medio
3 basso

COPRORATURA (id, tipo)
id tipo
1 magro
2 giusto
3 grasso

PERSONA (id, id_altezza, id_corporatura, nome, ecc.)
id id_altezza id_corporatura, ...
1 1 1
2 1 2
3 3 1
ecc.

Con una query vorrei tirare fuori un qadro complessivo del genere

------------------------------------------------------------------------------------------
|             |   alto     |  medio       |   basso              |
------------------------------------------------------------------------------------------
|     magro   |     n      |      n       |       n          |
------------------------------------------------------------------------------------------
|     giusto  |     n      |      n       |       n          |
-----------------------------------------------------------------------------------------
|     grasso  |     n      |      n       |       n          |
------------------------------------------------------------------------------------------ 

Come posso scrivere una query che mi tiri fuori una matrice simile??

[james]

Ho provato con una cosa simile, ma manca la meta della matrice:

SELECT COUNT(*), altezza.tipo, corporatura.tipo
FROM persona INNER JOIN altezza ON persona.id_altezza=altezza.id
INNER JOIN corporatura ON persona.id_corporatura=corporatura.id
GROUP BY persona.id_altezza; 

Con questa query riesco ad avere una lista delle tre altezze possibili e quante persone rientrano in ciascuna altezza, ma non riesco a scrivere una query che mi generi una matrice come quella di cui ho bisogno

[nicola75ss]

per ottenere il raggruppamento per peso e statura basta che aggiungi anche l'altro campo al group by

GROUP BY persona.id_altezza,persona.id_corporatura

per visualizzare i dati come vuoi tu dovresti farlo col php.

edit. In alternativa potresti crearti una tabella temporanea e poi su questa fare una query a campi incrociati.

create temporary table incrociata
SELECT COUNT(*) as quanti, altezza.tipo as altezza, corporatura.tipo as peso
FROM persona INNER JOIN altezza ON persona.id_altezza=altezza.id
INNER JOIN corporatura ON persona.id_corporatura=corporatura.id
GROUP BY persona.id_altezza,persona.id_corporatura

e poi fare una query così

select peso,
sum(case altezza WHEN "alto" then quanti else 0 end) AS alto,
sum(case altezza WHEN "medio" then quanti else 0 end) AS medio,
sum(case altezza WHEN "basso" then quanti else 0 end) AS basso
from incrociata
group by peso

fammi sapere.

[james]

Originariamente inviato da nicola75ss
per ottenere il raggruppamento per peso e statura basta che aggiungi anche l'altro campo al group by

GROUP BY persona.id_altezza,persona.id_corporatura

per visualizzare i dati come vuoi tu dovresti farlo col php.

edit. In alternativa potresti crearti una tabella temporanea e poi su questa fare una query a campi incrociati.

create temporary table incrociata
SELECT COUNT(*) as quanti, altezza.tipo as altezza, corporatura.tipo as peso
FROM persona INNER JOIN altezza ON persona.id_altezza=altezza.id
INNER JOIN corporatura ON persona.id_corporatura=corporatura.id
GROUP BY persona.id_altezza,persona.id_corporatura

e poi fare una query così

select peso,
sum(case altezza WHEN "alto" then quanti else 0 end) AS alto,
sum(case altezza WHEN "medio" then quanti else 0 end) AS medio,
sum(case altezza WHEN "basso" then quanti else 0 end) AS basso
from incrociata
group by peso

fammi sapere.

provo subito, ieri nonostante le ricerche non ne sono venuto a capo.

L'unica cosa che mi viene in mente è, la tabella dopo la query la devo eliminare giusto? E cosa succede se due utenti aprono la pagina che fa questo report un momento dopo l'altro e il sistema va a creare una tabella che già esiste perchè lo script in esecuzione dal primo utente non è ancora arrivato alla eliminazione della tabella?? Inoltre, se posso, in che modo organizzeresti i dati in php??

[nicola75ss]

prima di eseguire le due query metti questa nello script
drop table if exists incrociata

per visualizzare i dati della tabella incrociata si procede come per qualsiasi altra tabella. prova, se poi hai problemi ti aiuto.

P.S. Le query funzionano ma non so quanto si possa considerare efficiente un procedimento simile. Attendi pareri più autorevoli in merito.

[nicola75ss]

$query = mysql_query("drop table if exists incrociata") or die(mysql_error() );

$query2 = mysql_query("CREATE TEMPORARY TABLE incrociata
                      SELECT COUNT(*) as quanti, altezza.tipo as altezza, corporatura.tipo as peso
                      FROM persona 
                      INNER JOIN altezza ON persona.id_altezza=altezza.id 
                      INNER JOIN corporatura ON persona.id_corporatura=corporatura.id
                      GROUP BY persona.id_altezza,persona.id_corporatura") or die(mysql_error() );
                      
$query3 = mysql_query("select peso,
                       sum(case altezza WHEN \"alto\" then quanti else 0 end) AS alto,
                       sum(case altezza WHEN \"medio\" then quanti else 0 end) AS medio,
                       sum(case altezza WHEN \"basso\" then quanti else 0 end) AS basso
                       from incrociata
                       group by peso") or die(mysql_error() );


echo '<table>';  
while ($row = mysql_fetch_assoc($query3) ) {
echo '<tr>';
echo "<td>$row[peso]</td>";
echo "<td>$row[alto]</td>";
echo "<td>$row[medio]</td>";
echo "<td>$row[basso]</td>";
echo '</tr>';
} 
echo '<table>'; 

[james]

Originariamente inviato da nicola75ss
per ottenere il raggruppamento per peso e statura basta che aggiungi anche l'altro campo al group by

GROUP BY persona.id_altezza,persona.id_corporatura

per visualizzare i dati come vuoi tu dovresti farlo col php.

edit. In alternativa potresti crearti una tabella temporanea e poi su questa fare una query a campi incrociati.

create temporary table incrociata
SELECT COUNT(*) as quanti, altezza.tipo as altezza, corporatura.tipo as peso
FROM persona INNER JOIN altezza ON persona.id_altezza=altezza.id
INNER JOIN corporatura ON persona.id_corporatura=corporatura.id
GROUP BY persona.id_altezza,persona.id_corporatura

e poi fare una query così

select peso,
sum(case altezza WHEN "alto" then quanti else 0 end) AS alto,
sum(case altezza WHEN "medio" then quanti else 0 end) AS medio,
sum(case altezza WHEN "basso" then quanti else 0 end) AS basso
from incrociata
group by peso

fammi sapere.

ho fatto le due query, assegnato i privilegi per creare tabelle temporanee, ma se eseguo le query dal php ho un errore di query, se le faccio dal phpmyadmin funzionano...perchè??

Ho provato a scrivere le query di seguito, nella stessa variabile separandole dal ; ma mi dava errore, ho provato a scriverle in due variabili diverse e richiamarle con due chiamate al db diverse, ma non funziona, credo perchè le tabelle temporanee muoiono subito, sbaglio??
Ma allora come si eseguono queste query dal php? La prima query, quella che crea la tabella temporanea, da sola funziona.

[nicola75ss]

Hai provato a fare copia incolla del codice che ti ho postato?
Se hai messo nel codice l'ultima query senza i caratteri di escape, l'errore è sicuramente quello.

[james]

Originariamente inviato da nicola75ss
Hai provato a fare copia incolla del codice che ti ho postato?
Se hai messo nel codice l'ultima query senza i caratteri di escape, l'errore è sicuramente quello.

I caratteri di escape c'erano, il problema è che per fare le query usavo una funzione che ogni volta apriva e chiudeva la connessione a mysql, quindi, correggimi se sbaglio, chiudendo e riaprendo la connessione a mysql, perdevo la tabella in quanto è temporanea...giusto?

A parte questo però, questo sistema funziona se io conosco in maniere predeterminata la lista dei tipi di altezza, ma se un giorno aggiungo una nuova altezza, devo modificare lo script, a meno che non genero la seconda query in maniera dinaminca con un ciclo prendendo i valori dalla prima query

[nicola75ss]

Esatto, la tabella temporanea scompare quando cessa la connessione a mysql.

Per il secondo problema, se ti è possibile vedi di pianificare in anticipo il tipo di dati che dovrà contenere in modo da non dover rimettere mano alla query. Comunque per aggiungere una nuova statura basta inserire un'altra riga alla tua query. Sicuramente si può rendere dinamica ma adesso non ho il tempo di lavorarci su. Prova tu a farlo.