Newsgroup: it.comp.software.database
Titolo: Query difficile (per me)

create table candidati ( c_key int primary key, c_name varchar(99) )
create table esami ( e_key int primary key, p_candidate int, e_date
datetime, e_score int )
Come faccio ad ottenere una lista dei candidati, che all'ultimo esame
sostenuto hanno ottenuto un punteggio di almeno 6?
Finora me la sono sempre cavata, in casi simili, con tabelle temporanee, ma
vorrei una soluzione con GROUP BY / HAVING, piu' elegante possibile.
La domanda ha soprattutto scopo didattico, non sapete come mi scoccia non
saper usare a fondo questa funzionalita'...
AS

 sostenuto hanno ottenuto un punteggio di almeno 6?
Ciao Amedeo,
ti propongo un paio di soluzioni per SQL Server 2000/2005, la prima basata 
su una tabella derivata mentre la seconda su una Common Table Expression:
USE tempdb;
GO
CREATE TABLE dbo.candidati
(
    c_key int NOT NULL PRIMARY KEY,
    c_name varchar(10) NOT NULL
);
CREATE TABLE dbo.esami
(
    e_key int NOT NULL PRIMARY KEY,
    p_candidate int NOT NULL,
    e_date datetime NOT NULL,
    e_score tinyint NOT NULL,
    CONSTRAINT FK_esami_candidati FOREIGN KEY(p_candidate)
    REFERENCES dbo.candidati(c_key),
    CONSTRAINT FK_score CHECK(e_score BETWEEN 1 AND 10)
);
GO
INSERT INTO dbo.candidati VALUES(1, 'Lorenzo');
INSERT INTO dbo.candidati VALUES(2, 'Luca');
INSERT INTO dbo.candidati VALUES(3, 'Andrea');
INSERT INTO dbo.candidati VALUES(4, 'Gianluca');
INSERT INTO dbo.candidati VALUES(5, 'Marcello');
INSERT INTO dbo.esami VALUES(1, 1, '20061001', 5);
INSERT INTO dbo.esami VALUES(2, 1, '20061002', 6);
INSERT INTO dbo.esami VALUES(3, 1, '20061003', 7);
INSERT INTO dbo.esami VALUES(4, 2, '20061001', 8);
INSERT INTO dbo.esami VALUES(5, 2, '20061002', 4);
INSERT INTO dbo.esami VALUES(6, 3, '20061001', 6);
INSERT INTO dbo.esami VALUES(7, 3, '20061002', 6);
INSERT INTO dbo.esami VALUES(8, 5, '20061001', 4);
INSERT INTO dbo.esami VALUES(9, 5, '20061001', 6);
INSERT INTO dbo.esami VALUES(10, 5, '20061001', 8);
GO
/* Soluzione SQL Server 2000 */
SELECT C.c_name, Q.e_date
FROM dbo.candidati AS C
INNER JOIN (
    SELECT p_candidate, MAX(e_date) AS e_date
    FROM dbo.esami
    WHERE e_score >= 6
    GROUP BY p_candidate
) AS Q
ON C.c_key = Q.p_candidate;
/* Soluzione SQL Server 2005 */
WITH CTE_esami AS
(
    SELECT p_candidate, MAX(e_date) AS e_date
    FROM dbo.esami
    WHERE e_score >= 6
    GROUP BY p_candidate
)
SELECT C.c_name, CTE.e_date
FROM dbo.candidati AS C
INNER JOIN CTE_esami AS CTE
ON C.c_key = CTE.p_candidate;
GO
/* Output:
c_name     e_date
---------- -----------------------
Lorenzo    2006-10-03 00:00:00.000
Luca       2006-10-01 00:00:00.000
Andrea     2006-10-02 00:00:00.000
Marcello   2006-10-01 00:00:00.000
(4 row(s) affected)
*/
DROP TABLE dbo.esami, dbo.candidati;
Ciao!
-- 
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org
 

 GO
Io uso Sybase ASE 12.5, ma quello che mi preme e' capire il concetto, per
cui apprezzerei al massimo una soluzione completamente portabile.
Mi sembra che entrambe le soluzioni proposte passino, in un modo o
nell'altro, attraverso subqueries/tabelle temporanee (anche se create
implicitamente alla query).
E' possibile farlo con una SELECT "semplice" (non importa efficiente),
oppure e' qualcosa che non si puo' fare (e posso mettermi il cuore in pace
per non esserci mai riuscito)?
AS

 pace per non esserci mai riuscito)?
Secondo me non è possibile, o per lo meno, a me non viene in mente una 
soluzione del genere :-)


 soluzione del genere :-)
Questa seoluzione non utilizza tabelle derivate:
SELECT C.c_name, MAX(E.e_date) AS e_date
FROM dbo.candidati AS C
INNER JOIN dbo.esami AS E
ON C.c_key = E.p_candidate
WHERE E.e_score >= 6
GROUP BY C.c_name;
/* Output:
c_name     e_date
---------- -----------------------
Andrea     2006-10-02 00:00:00.000
Lorenzo    2006-10-03 00:00:00.000
Luca       2006-10-01 00:00:00.000
Marcello   2006-10-01 00:00:00.000
(4 row(s) affected)
*/
Ciao!


 (4 row(s) affected)
Grazie mille, questa soluzione e' quello che cercavo. Confermo che funziona
anche su ASE (e per me che sono ligure, il nome di questo db e' tutto un
programma)    :-)
E' chiaramente elegante.
Devo studiarmi la INNER JOIN che non ho mai capito bene, ma non c'e'
problema   :-)
Inoltre mi hai confermato che GROUP/HAVING da solo non puo' ottenere quel
risultato, sono contento di saperlo e ti ringrazio.
AS

 programma)    :-)
Mi devo correggere, non ci siamo.
Questa query ritorna anche Luca, che ha ricevuto nell'ordine 8 e 4, e quindi
non dovrebbe apparire nella lista (l'ultimo esame sostenuto ha risultato 4).
AS

 AS
marc.
-- 
Marcello Poletti
Dingo&Epomops
Microsoft MVP - SQL Server
http://www.dingoepomops.it
http://blogs.dotnethell.it/epomops/

 risultato 4).
Ciao Amedeo,
OK, l'importante è intendersi, avevo interpretato diversamente la tua 
domanda, pensavo volessi la data dell'ultimo esame in cui il candidato 
avesse superato l'esame :-)
Direi che la soluzione proposta da Marcello soddisfa la tua richiesta (anche 
se necessita di una tabella derivata, una vista o una CTE per calcolare le 
date relative all'ultimo esame sostenuto da ogni candidato).


 marc.
rimarc.
