[DB] INTERROGAZIONE DIFFICILE

[lucajeck]

ciao a tutti, ho un problema con una query non riesco proprio a farla:

Io ho questo schema relazionale

Esame(nome, prezzo) //nome è chiave
Prescrizioni(codice, data, cliente, medico) //codice è chiave
Esami-Prescritti( prescrizioni, esame) //entrambi sono chiave
Cliente(cod-fisc, nome, città) //cod-fisc è chiave
Medico(cod-fisc, nome, città) //cod_fisc è chiave

Lo schema rappresenta informazioni sui clienti che si rivolgono al laboratorio per esami clinici, e sui medici che prescrivono tali indagini. Nella relazione Prescrizione sono indicati il medico, il cliente e la data di ciascuna prescrizione- In Esami-Prescritti è rappresentato l'elenco degli esami indicati in ciascuna prescrizione.

Vorrei che qualcuno mi aiutasse a formulare la seguente interrogazione in SQL:
I nomi dei clienti che hanno presentato una prescrizione contenente esami tutti dello stesso prezzo.
GRAZIE A TUTTI!! :-)

[shinya]

Presumo che il 'codice' in Prescrizioni sia lo stesso che metti in chiave in esami-prescritti, che esame nella stessa tabella sia 'nome' in Esame, ecc...cioè che le tabelle siano legate con foreign key appropriate...questo dovrebbe andare...(non ho provato!)

select c.nome
  from cliente c join prescrizioni p on c.cod_fisc = p.cliente
  join esami_prescritti ep on p.codice = ep.prescrizioni
  join esame e on e.nome = ep.esame
where e.prezzo = IL_PREZZO_CHE_TI_INTERESSA

[lucajeck]

Innanzi tutto GRAZIE MILLE PER LA RISPOSTA!!!
Tutte le tue supposizioni sono giuste, ma quando dico che voglio i nomi dei clienti che hanno presentato una prescrzione contenente esami tutti dello stesso prezzo, intendo non di un prezzo in particolare, ma che tutte i prezzi degli esami devono essere tutti uguali. E' uno dei 5 punti di un'esercizio di un'appello d'esame e non riesco proprio a risolverlo.......Grazie e buona giornata!

[Frank1962]

Originariamente inviato da lucajeck

Innanzi tutto GRAZIE MILLE PER LA RISPOSTA!!!
Tutte le tue supposizioni sono giuste, ma quando dico che voglio i nomi dei clienti che hanno presentato una prescrzione contenente esami tutti dello stesso prezzo, intendo non di un prezzo in particolare, ma che tutte i prezzi degli esami devono essere tutti uguali. E' uno dei 5 punti di un'esercizio di un'appello d'esame e non riesco proprio a risolverlo.......Grazie e buona giornata!

penso allora che ci devi aggiungere una SELECT annidata con GROUP BY del tipo:

select c.nome
  from cliente c join prescrizioni p on c.cod_fisc = p.cliente
  join esami_prescritti ep on p.codice = ep.prescrizioni
  join esame e on e.nome = ep.esame
where e.prezzo = ANY (SELECT Prezzo FROM Esame GROUP BY Prezzo) 

[lucajeck]

grazie mille, pomeriggio lo provo :-)

buona giornata

[trallallero]

Originariamente inviato da Frank1962

penso allora che ci devi aggiungere una SELECT annidata con GROUP BY del tipo:

select c.nome
  from cliente c join prescrizioni p on c.cod_fisc = p.cliente
  join esami_prescritti ep on p.codice = ep.prescrizioni
  join esame e on e.nome = ep.esame
where e.prezzo = ANY (SELECT Prezzo FROM Esame GROUP BY Prezzo) 

SELECT DISTINCT Prezzo FROM Esame

no ?

[guldo76]

Originariamente inviato da Frank1962

penso allora che ci devi aggiungere una SELECT annidata con GROUP BY del tipo:

select c.nome
  from cliente c join prescrizioni p on c.cod_fisc = p.cliente
  join esami_prescritti ep on p.codice = ep.prescrizioni
  join esame e on e.nome = ep.esame
where e.prezzo = ANY (SELECT Prezzo FROM Esame GROUP BY Prezzo)

Anche scrivendola come dice trallallero, a cosa serve?!?
Il prezzo, preso dalla tabella Esame, sarà sempre uguale a qualunque prezzo della tabella Esame! Non credete?!?

IMHO:

SELECT Cliente.nome, Count(QCP.codCliente)
FROM Cliente
INNER JOIN  (
	SELECT DISTINCT Cliente.codCliente, Cliente.nome, Esame.prezzo
	FROM Cliente
	INNER JOIN Prescrizioni
		INNER JOIN EsamiPrescritti
			INNER JOIN Esame
			ON Esame.codEsame = EsamiPrescritti.codEsame
		ON Prescrizioni.codPrescrizione = EsamiPrescritti.codPrescrizione
	ON Cliente.codCliente = Prescrizioni.codCliente 
) AS QCP
ON Cliente.codCliente = QCP.codCliente
GROUP BY Cliente.nome
HAVING Count(QCP.codCliente) = 1
ORDER BY Cliente.nome;

dove per chiarezza ho rinominato tabelle e campi così:

Esame(codEsame, prezzo)
Prescrizioni(codPrescrizione, data, codCliente, codMedico)
EsamiPrescritti(codPrescrizione, codEsame)
Cliente(codCliente, nome, citta)
Medico(codMedico, nome, citta)

[trallallero]

Originariamente inviato da guldo76

Anche scrivendola come dice trallallero, a cosa serve?!?

no scusa, il mio era solo un altro modo, piu standard e leggibile. Non ho letto tutto il 3d attentamente perché non sono abituato a vedere tutti quei join. Io uso Oracle e si scrive in un altro modo.
Ho solo notato quella GROUP BY perché era in neretto

[trallallero]

allora, l'ho letto. Potresti lavorare per MIN(prezzo)
Ottieni il MIN(Prezzo) per ogni cliente e poi cerchi i clienti che hanno solo quel prezzo.
Scegli i clienti che nei loro prezzi hanno solo il proprio MIN.
Non so se mi sono capito bene

[trallallero]

dimenticavo...
ovviamente devi cercare solo quelli che hanno solo quel MIN(prezzo)
quindi devi mettere il MIN in un:

AND NOT EXISTS (
SELECT 1 FROM ...
WHERE prezzo <> SELECT MIN(...)
...

[guldo76]

Credo che basti questo:

SELECT Cliente.codCliente, Cliente.nome, count(distinct Esame.prezzo)
FROM Cliente
INNER JOIN Prescrizioni
	INNER JOIN EsamiPrescritti
		INNER JOIN Esame
		ON Esame.codEsame = EsamiPrescritti.codEsame
	ON Prescrizioni.codPrescrizione = EsamiPrescritti.codPrescrizione
ON Cliente.codCliente = Prescrizioni.codCliente 
GROUP BY Cliente.codCliente, Cliente.nome
HAVING count(distinct Esame.prezzo) = 1
ORDER BY Cliente.nome;

senza bisogno di fare un self join su Clienti, che diventa necessario se il database non supporta il count(distinct campo).

[shinya]

Originariamente inviato da trallallero

Io uso Oracle e si scrive in un altro modo.

Anch'io uso oracle, ma i join li scrivo proprio così!!
Sei mica all'infame versione 8?? :P

[trallallero]

Originariamente inviato da shinya

Anch'io uso oracle, ma i join li scrivo proprio così!!
Sei mica all'infame versione 8?? :P

lavoriamo anche su 9 (tra un pò avremo anche il 10) ma continuiamo a non usare quella sintassi.
Inner join è di default, non serve specificarlo, l'outer è (+) dopo il campo. Questo dopo la WHERE tra i campi di tabelle diverse perchè devo dire che non sono pratico delle JOIN dentro la clausola FROM. So che non è supportata prima della versione 9 ma non l'ho mai dovuta usare. E non ne vedo l'utilità ... ma mi incuriosisce quindi indagherò.
Ciao