[MySql] Urgentissimo aiuto con qualche query

[D4rkAng3l]

Ragazzi ci siamo...domani mattina non sò come dovrò consegnare sto benedetto progetto di base di dati...ho quasi finito...mi manca solo fare qualche query (parecchie a dir la verità ma qualcuna già l'ho fatta per conto mio)

Ce n'è una che non mi riesce (in realtà è solo un pezzo di una query più grande).

Se io ho una tabella così fatta:

create table OFFERTA
(
Id_Offerta int not null auto_increment primary key,
Partecipante varchar(20) not null,
Id_Oggetto int not null,
Valore_Offerta decimal(6,2) not null,
Data datetime not null,
foreign key (Partecipante) references UTENTE(User_Id),
foreign key (Id_Oggetto) references INSERZIONE(Id_Oggetto)
) type=InnoDB;

che contiene dati relativi alle offerte che gli utenti fanno su certi oggetti (sistema di aste online) del tipo:

(NULL, 'Salvo', 4, 5.50, '2008-02-20 14:09:06'),
(NULL, 'Marilu', 4, 7.50, '2008-02-27 18:12:06'),
(NULL, 'Salvo', 4, 10.25, '2008-03-04 23:55:06'),
(NULL, 'Plutone', 15, 3.75, '2008-02-28 22:15:07'),
(NULL, 'Devilboy', 15, 6.25, '2008-03-05 17:15:23'),
(NULL, 'Plutone', 15, 8.00, '2008-03-13 22:13:07'),
(NULL, 'FreeMan', 15, 8.37, '2008-03-14 23:32:55');

vabbè i NULL iniziali ci stanno solo perchè è preso dalle mie insert. Il terzo numero corrisponde al campo Id_Oggetto e si riferisce ad un oggetto per cui si stà effettuando l'offerta, il campo successivo è quanto gli utenti hanno offerto nell'asta.

Vorrei sapere come faccio a sapere in questa tabella qual'è stato il prezzo maggiore (quindi del campo Valore_Offerta) per l'oggetto con campo Id_Oggetto pari a 4?

Vi prego è importantissimo...se non consegno entro domani devo fare l'orale a Giugno

Grazie
Andrea

[D4rkAng3l]

doh quella cosa l'ho risolta...però il problema ora è fare tutta la query che dovrei fare...vi prego help me sono alla frutta

Mettiamo che io ho 3 tabelle: UTENTE (che contiene gli utenti), INSERZIONE che contiene le inserzioni (ed ogni inserzione ha una sua data di scadenza) ed OFFERTA che contiene le offerte che ogni utente fà per un'inserzione.
Se voglio sapere chi ha vinto un'asta devo chiedermi qual'è l'utente che ha fatto l'offerta massima precedente alla data di scadenza (senza usare query ricorsivoe possibilmente).

quindi le mie 3 tabelle sono:

create table UTENTE
(
User_Id varchar(20) not null primary key,
Nome varchar(20) not null,
Cognome varchar(20) not null,
Data_Nascita date not null,
Indirizzo varchar(50) not null,
Cap int not null
) type=InnoDB;



create table INSERZIONE
(
Id_Oggetto int not null auto_increment primary key,
Titolo varchar(60) not null,
Descrizione text,
Costo_Base decimal(6,2),
Scadenza datetime,
Compralo_Subito float(4,2),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Id_Categoria varchar(30) not null references CATEGORIA(Id_Categoria)
) type=InnoDB;



create table OFFERTA
(
Id_Offerta int not null auto_increment primary key,
Partecipante varchar(20) not null,
Id_Oggetto int not null,
Valore_Offerta float(4,2) not null,
Data datetime not null,
foreign key (Partecipante) references UTENTE(User_Id),
foreign key (Id_Oggetto) references INSERZIONE(Id_Oggetto)
) type=InnoDB;

La query vera e propria che non riesco a fare è: Per l'inserzione con Id_Oggetto pari a 4 chi è l'utente che ha fatto l'offerta più alta che però ha data precedente alla data di scadenza dell'inserzione?

Vi pregoooo sono disperatissimo

Grazie

[gugoXX]

La risposta alla tua domanda e' in questo thread.
Non pubblico la tua versione senno' Cionci dice che ti ho aiutato troppo.

http://www.hwupgrade.it/forum/showthread.php?t=1680031

[D4rkAng3l]

Originariamente inviato da gugoXX

La risposta alla tua domanda e' in questo thread.
Non pubblico la tua versione senno' Cionci dice che ti ho aiutato troppo.

http://www.hwupgrade.it/forum/showthread.php?t=1680031

Ti pregoooo Sono a quota meno 13...stò proprio indietrissimo...se me la pubblichi ti sarei gratissimo

[gugoXX]

Vorrei sapere come faccio a sapere in questa tabella qual'è stato il prezzo maggiore (quindi del campo Valore_Offerta) per l'oggetto con campo Id_Oggetto pari a 4?

Allora, te lo metto, pero' lo leggi tutto e cerchi di capirlo.

Questo e' il complete SET di tutti gli oggetti e di ciascuna sua offerta.

SELECT *
FROM offerta JOIN inserzione
ON (offerta.id_oggetto = inserzione.id_oggetto)

questo e' il set filtrato che ci interessa, ovvero tutto e solo quanto riguarda l'oggetto 4, prima della sua scadenza.

SELECT *
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza

Ci sei? Ora dobbiamo cercare il massimo dell id_offerta, che e' anche la piu' recente, per costruzione.

SELECT MAX(id_offerta)
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza

Bene, ora dobbiamo prendere tutto di questa offerta, cosi' leggiamo anche il valore che ci serve.

SELECT ext.* FROM offerta as ext
WHERE ext.id_offerta = 
(SELECT MAX(id_offerta)
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza
)

Pensa se ti fossi chiesto:
Per ciascun oggetto, dire qual e' la sua massima offerta, entro il suo tempo di scadenza.

[D4rkAng3l]

Originariamente inviato da gugoXX

Allora, te lo metto, pero' lo leggi tutto e cerchi di capirlo.

Questo e' il complete SET di tutti gli oggetti e di ciascuna sua offerta.

SELECT *
FROM offerta JOIN inserzione
ON (offerta.id_oggetto = inserzione.id_oggetto)

questo e' il set filtrato che ci interessa, ovvero tutto e solo quanto riguarda l'oggetto 4, prima della sua scadenza.

SELECT *
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza

Ci sei? Ora dobbiamo cercare il massimo dell id_offerta, che e' anche la piu' recente, per costruzione.

SELECT MAX(id_offerta)
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza

Bene, ora dobbiamo prendere tutto di questa offerta, cosi' leggiamo anche il valore che ci serve.

SELECT ext.* FROM offerta as ext
WHERE ext.id_offerta = 
(SELECT MAX(id_offerta)
   FROM offerta JOIN inserzione
      ON (offerta.id_oggetto = inserzione.id_oggetto)
  WHERE inserzione.id_oggetto=4 
     AND offerta.data<=inserzione.scadenza
)

Pensa se ti fossi chiesto:
Per ciascun oggetto, dire qual e' la sua massima offerta, entro il suo tempo di scadenza.

mmm si può fare senza query ricorsive questo o servono per forza?

[gugoXX]

Originariamente inviato da D4rkAng3l

mmm si può fare senza query ricorsive questo o servono per forza?

Non e' una query ricorsiva.
E' un'autojoin, ed e' perfettamente "legale" e supportata da tutti i motori DB Relazionali, compeso ACCESS.
E comunque in linguaggio standard SQL92 non si puo' evitare l'autojoin per risolvere questo problema.

Una query ricorsiva e' molto piu' complessa, e non serve per risolvere questo problema.

Se la vuoi in linguaggio non standard, per MySql
Ovvero prendo il primo record del set filtrato, una volta che l'ho ordinato per id_offerta decrescente.

SELECT offerta.*
FROM offerta JOIN inserzione
ON (offerta.id_oggetto = inserzione.id_oggetto)
WHERE inserzione.id_oggetto=4
AND offerta.data<=inserzione.scadenza
ORDER BY id_offerta DESC
LIMIT 1

[D4rkAng3l]

Originariamente inviato da gugoXX

Non e' una query ricorsiva.
E' un'autojoin, ed e' perfettamente "legale" e supportata da tutti i motori DB Relazionali, compeso ACCESS.
E comunque in linguaggio standard SQL92 non si puo' evitare l'autojoin per risolvere questo problema.

Una query ricorsiva e' molto piu' complessa, e non serve per risolvere questo problema.

Se la vuoi in linguaggio non standard, per MySql
Ovvero prendo il primo record del set filtrato, una volta che l'ho ordinato per id_offerta decrescente.

mmm scusa mi sento cretino io però non sono abiturato ad usare la sintassi con i JOIN e mi confonde un pochino

[gugoXX]

Quella e' la sintassi corretta.
JOIN sui campi di JOIN
WHERE sui filtri.

E possono essere usate entrambe, come nell'esempio postato.

[D4rkAng3l]

Originariamente inviato da gugoXX

Quella e' la sintassi corretta.
JOIN sui campi di JOIN
WHERE sui filtri.

E possono essere usate entrambe, come nell'esempio postato.

ok...nel frattempo me lo sono rivisto...si hai ragione...tnx

Ah altra cosa...come si faceva a vedere tutte le sottocategorie di una certa categoria...era quel discorso che bisognava usare per forza una query ricorsiva

create table CATEGORIA
(
Id_Categoria int not null auto_increment primary key,
Nome_Cat varchar(40) not null,
Descrizione varchar(255) not null,
Padre int references CATEGORIA(Id_Categoria)
) type=InnoDB;

[gugoXX]

Originariamente inviato da D4rkAng3l

ok...nel frattempo me lo sono rivisto...si hai ragione...tnx

Ah altra cosa...come si faceva a vedere tutte le sottocategorie di una certa categoria...era quel discorso che bisognava usare per forza una query ricorsiva

create table CATEGORIA
(
Id_Categoria int not null auto_increment primary key,
Nome_Cat varchar(40) not null,
Descrizione varchar(255) not null,
Padre int references CATEGORIA(Id_Categoria)
) type=InnoDB;

Ecco, quella lascia stare.
Bisogna utilizzare costrutti non standard e sicuramente non te la chiede.

Chiediti al massimo chi sono i figli e chi e' il padre di ciascuna categoria.

[D4rkAng3l]

Originariamente inviato da gugoXX

Ecco, quella lascia stare.
Bisogna utilizzare costrutti non standard e sicuramente non te la chiede.

Chiediti al massimo chi sono i figli e chi e' il padre di ciascuna categoria.

Ok...anche quelle le ho aggiunte e sono carine perchè le ho fatte duplicando la tabella CATEGORIA in PADRE e FIGLIA con gli alias che tanto le piaccino...quindi spero che apprezzi.

Lo sò che sono un rompiballe ma capiscimi che sono disperato...se avessi altri 3 giorni mi ci spaccherei la testa da solo finchè non riesco...giuro.

Se consideriamo queste due tabelle: INSERZIONE e CATEGORIA

create table INSERZIONE
(
Id_Oggetto int not null auto_increment primary key,
Titolo varchar(60) not null,
Descrizione text,
Costo_Base float(4,2),
Scadenza datetime,
Compralo_Subito float(4,2),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Id_Categoria varchar(30) not null references CATEGORIA(Id_Categoria)
) type=InnoDB;

create table CATEGORIA
(
Id_Categoria int not null auto_increment primary key,
Nome_Cat varchar(40) not null,
Descrizione varchar(255) not null,
Padre int references CATEGORIA(Id_Categoria)
) type=InnoDB;

Per vedere tutte le inserzioni farei semplicemente:

SELECT INSERZIONE.Titolo, INSERZIONE.Scadenza
FROM INSERZIONE;

però se le volessi vedere in ordine di data di scadenza e magari usando la limit le 3 inserzioni più vicini a scadere...ho provato ad usare order by ma mi dà errore...booo

[D4rkAng3l]

ah no come non detto...risolto...è sche scrivevo ordered by e non order by...ormai stò fuso...mai stato davanti ad un pc come negli ultimi 3 giorni...

[gugoXX]

Originariamente inviato da D4rkAng3l

però se le volessi vedere in ordine di data di scadenza e magari usando la limit le 3 inserzioni più vicini a scadere...ho provato ad usare order by ma mi dà errore...booo

Guarda che la LIMIT non e' standard SQL.
Magari si arrabbia.

[D4rkAng3l]

Originariamente inviato da gugoXX

Guarda che la LIMIT non e' standard SQL.
Magari si arrabbia.

mmm in classe l'abbiamo usata...cmq per sicurezza gli ho fatto due esempi uno usandola e l'altro non usandola...

Ne mancano sempre meno...forse gliela fò...forse...devo scrivere ancora la relazione stanotte....

Se ho questa tabella

create table FEEDBACKVENDITORE
(
Id_Oggetto int not null references INSERZIONE(Id_Oggetto),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Id_Acquirente varchar(20) not null references UTENTE(User_Id),
Feedback enum('Positivo', 'Neutro', 'Negativo') not null,
Commento varchar(255),
primary key (Id_Oggetto)
) type=InnoDB;

Praticamente la chiave come avevamo detto è solo l'ID dell'oggetto in quanto il feedback dato è relativo all'oggetto venduto.

Se io volessi vedere i feedback che ha ricevuto un venditore nel seguente modo:

POSITIVI: 3
NEGATIVI: 1
NEUTRI: 0

credo che devo usare il group by ma non mi viene come usarlo...please help a poor boy in panic
Cioè erano cose che sapevo anche fare quando feci lo scritto ma ormai sono fuso...non ragiono più...

[gugoXX]

Questa in SQL Standard e' un po' un casino ed e' davvero bruttina.
In SQL standard devi fare un autojoin 3 volte, ciascuna delle quali prendi il COUNT(*) WHERE Feedback='Positivo'

e un altra query in join dove prendi WHERE Feedback='Negativo'

Ma nessuno la farebbe in SQL Standard.

In SQL Compatibile anche con MySql (buono anche per Oracle questa) si potrebbe usare la funzione CASE, anche se magari ci sono altri costrutti.

SELECT id_venditore,
COUNT(CASE feedback='Positivo' THEN 1 ELSE NULL END) as cntpos,
COUNT(CASE feedback='Negativo' THEN 1 ELSE NULL END) as cntneg,
COUNT(CASE feedback='Neutro' THEN 1 ELSE NULL END) as cntneu
FROM FEEDBACKVENDITORE
GROUP BY id_venditore;

Ma ora ho la pizza... ciau...

[D4rkAng3l]

Originariamente inviato da gugoXX

Questa in SQL Standard e' un po' un casino ed e' davvero bruttina.
In SQL standard devi fare un autojoin 3 volte, ciascuna delle quali prendi il COUNT(*) WHERE Feedback='Positivo'

e un altra query in join dove prendi WHERE Feedback='Negativo'

Ma nessuno la farebbe in SQL Standard.

In SQL Compatibile anche con MySql (buono anche per Oracle questa) si potrebbe usare la funzione CASE, anche se magari ci sono altri costrutti.

SELECT id_venditore,
COUNT(CASE feedback='Positivo' THEN 1 ELSE NULL END) as cntpos,
COUNT(CASE feedback='Negativo' THEN 1 ELSE NULL END) as cntneg,
COUNT(CASE feedback='Neutro' THEN 1 ELSE NULL END) as cntneu
FROM FEEDBACKVENDITORE
GROUP BY id_venditore;

Ma ora ho la pizza... ciau...

Oi ti ringrazio, sei stato veramente iper gentile...se non fossi di Torino te la offrirei io una pizza !!! Veramente

[D4rkAng3l]

Originariamente inviato da gugoXX

Questa in SQL Standard e' un po' un casino ed e' davvero bruttina.
In SQL standard devi fare un autojoin 3 volte, ciascuna delle quali prendi il COUNT(*) WHERE Feedback='Positivo'

e un altra query in join dove prendi WHERE Feedback='Negativo'

Ma nessuno la farebbe in SQL Standard.

In SQL Compatibile anche con MySql (buono anche per Oracle questa) si potrebbe usare la funzione CASE, anche se magari ci sono altri costrutti.

SELECT id_venditore,
COUNT(CASE feedback='Positivo' THEN 1 ELSE NULL END) as cntpos,
COUNT(CASE feedback='Negativo' THEN 1 ELSE NULL END) as cntneg,
COUNT(CASE feedback='Neutro' THEN 1 ELSE NULL END) as cntneu
FROM FEEDBACKVENDITORE
GROUP BY id_venditore;

Ma ora ho la pizza... ciau...

mmm non mi funziona...se eventualmente fai in tempo sapresti darmi la soluzione semplificata come per esempio una query che conta solo quanti sono i feedback positivi senza usare i CASE THEN che non li ha mai usati lei?

Tnx ;-)

[gugoXX]

Si' e' vero, c'era un errore di sintassi

SELECT id_venditore,
COUNT(CASE WHEN feedback='Positivo' THEN 1 ELSE NULL END) as cntpos,
COUNT(CASE WHEN feedback='Negativo' THEN 1 ELSE NULL END) as cntneg,
COUNT(CASE WHEN feedback='Neutro' THEN 1 ELSE NULL END) as cntneu
FROM FEEDBACKVENDITORE
GROUP BY  id_venditore;

Ad ogni modo contare solo i positivi ovviamente dovresti saperlo fare anche tu, e' una domanda classica da orale.

Conto quante righe ci sono tra quelle che hanno feedback positivo, raggruppate per ciascun venditore...

SELECT id_venditore,COUNT(*)
FROM FEEDBACKVENDITORE
WHERE feedback='Positivo' 
GROUP BY id_venditore;