Discussion :

[Pop Hip]

Bonjour à tous,
Voila, je suis coincé depuis quelques jours avec une requête pourtant simple d'apparence donc je solicite votre aide !

J'ai 3 tables ( enfin 3 nécessaires pour ma requête ) : Oeuvre, Exemplaire, et Emprunt.

Voici le code de création des tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
DROP TABLE IF EXISTS Emprunt;
DROP TABLE IF EXISTS Exemplaire;
DROP TABLE IF EXISTS Oeuvre;
 
CREATE TABLE Oeuvre (
	noOeuvre int AUTO_INCREMENT,
	titre varchar(50),
	dateParution date,
	PRIMARY KEY (noOeuvre)
) 	ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE Exemplaire (
	noExemplaire int AUTO_INCREMENT,
	etat ENUM("neuf", "bon", "moyen", "mauvais"),
	dateAchat date,
	prix float,
	noOeuvre int,
	CONSTRAINT fk_noOeuvre FOREIGN KEY (noOeuvre) REFERENCES Oeuvre (noOeuvre),
	PRIMARY KEY (noExemplaire)
)	ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE Emprunt (
	idAdherent int,
	noExemplaire int,
	dateEmprunt date,
	dateRendu date,
	CONSTRAINT fk_idAdherent FOREIGN KEY (idAdherent) REFERENCES Adherent (idAdherent),
	CONSTRAINT fk_noExemplaire FOREIGN KEY (noExemplaire) REFERENCES Exemplaire (noExemplaire),
	PRIMARY KEY (dateEmprunt, idAdherent, noExemplaire)
)	ENGINE=InnoDB DEFAULT CHARSET=utf8;

Donc rapidement : Une Oeuvre peut être référencée sans qu'il existe des exemplaires en stock ( ou empruntés ). La table Emprunts est un historique et dateRendu est NULL si le l'exemplaire est en cours d'emprunt.

J'aimerais pouvoir compter le nombre d'exemplaires disponibles par Oeuvre et pour l'instant j'ai ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
	Oeuvre.noOeuvre as id,
	titre, 
	COUNT(DISTINCT Exemplaire.noExemplaire) as nbExemplaire
FROM 
	Oeuvre 
	LEFT JOIN Exemplaire ON Exemplaire.noOeuvre=Oeuvre.noOeuvre
	LEFT JOIN Emprunt ON Emprunt.noExemplaire=Exemplaire.noExemplaire
GROUP BY
	id;

On a donc le nombre d'exemplaires par Oeuvre mais je ne sais pas quoi soustraire à ça pour enlever le nombres d'exemplaires en cours d'emprunt ( toujours par oeuvre ). Pensez vous que c'est faisable sans sous requêtes ?

Si vous avez quelques conseils la dessus je suis preneur

[acaumes]

Bonjour,

Le nombre d'exemplaires disponibles correspond aux exemplaires du stock moins ceux qui ont été empruntés.
Le nombre d'exemplaires empruntés c'est les exemplaires qui sont sortis (date d'emprunt non nulle à cause de la jointure gauche) et date de retour nulle (enfin si cette date est nulle tant que le retour n'est pas enregistré)

Donc je ferais quelque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
	Oeuvre.noOeuvre as id,
	titre,
	COUNT(DISTINCT Exemplaire.noExemplaire) as nbExemplaire,
        sum(case
                when emprunt.dateemprunt is not null and emprunt.daterendu is null then 1
                else 0
                end)
                as NBSortis,
	COUNT(DISTINCT Exemplaire.noExemplaire) -
        sum(case
                when emprunt.dateemprunt is not null and emprunt.daterendu is null then 1
                else 0
                end)
                as Disponibles
 
 
FROM
	Oeuvre
	LEFT JOIN Exemplaire ON Exemplaire.noOeuvre=Oeuvre.noOeuvre
	LEFT JOIN Emprunt ON Emprunt.noExemplaire=Exemplaire.noExemplaire
GROUP BY
	id, titre;

NB: Tu sembles utiliser MYSQL mais mets l'ensemble des colonnes qui ne sont pas aggrégées dans le group by même si dans ce cas, il y a redondance fonctionnelle.

Avec les données de test suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (1, 2, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (1, 10, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (1, 3, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (1, 5, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (1, 9, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (2, 2, '2016-01-01', '2016-01-29');
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (2, 6, '2016-02-01', '2016-02-28');
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (2, 7, '2016-02-01', '2016-03-21');
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (2, 4, '2016-02-01', NULL);
Insert into [Emprunt] (idAdherent, noExemplaire, dateEmprunt, dateRendu) Values (3, 2, '2015-12-01', '2016-12-31');
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (1, '1', '2016-01-01', 10.0, 1);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (2, '1', '2016-01-01', 10.0, 1);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (3, '1', '2016-01-01', 10.0, 1);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (4, '1', '2016-01-01', 10.0, 1);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (5, '1', '2016-01-01', 10.0, 2);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (6, '1', '2016-01-01', 10.0, 2);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (7, '1', '2016-01-01', 10.0, 2);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (8, '1', '2016-01-01', 10.0, 2);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (9, '1', '2016-01-01', 10.0, 3);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (10, '1', '2016-01-01', 10.0, 3);
Insert into [Exemplaire] (noExemplaire, etat, dateAchat, prix, noOeuvre) Values (11, '1', '2016-01-01', 10.0, 3);
Insert into [Oeuvre] (noOeuvre, titre, dateParution) Values (1, 'aaaaaa', '2012-01-01');
Insert into [Oeuvre] (noOeuvre, titre, dateParution) Values (2, 'bbbbbb', '2015-04-02');
Insert into [Oeuvre] (noOeuvre, titre, dateParution) Values (3, 'cccccc', '2016-10-14');

la requête que je te proposes donne les résultats suivants:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
id  titre     nbExemplaire  NBSortis  Disponibles                
1   "aaaaaa"  4             3         1                
2   "bbbbbb"  4             1         3                
3   "cccccc"  3             2         1

Cordialement

[Pop Hip]

Haaaaa niquel ! J'avais déjà testé un truc similaire avec un if à la place du case mais j'aurais jamais pensé au sum, du coup le COUNT comptait aussi quand le if me renvoyait false...

En tous cas merci beaucoup pour ton aide je marque le topic comme résolu

[aieeeuuuuu]

Bonjour,

Ceci devrait aussi fonctionner :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT 
	Oeuvre.noOeuvre as id,
	titre, 
	COUNT(Exemplaire.noExemplaire) - COUNT(Emprunt.noExemplaire) as nbExemplaireDispo
FROM 
	Oeuvre 
	LEFT JOIN Exemplaire 
		ON		Exemplaire.noOeuvre=Oeuvre.noOeuvre
	LEFT JOIN Emprunt 
		ON		Emprunt.noExemplaire=Exemplaire.noExemplaire 
		AND		Emprunt.daterendu IS NULL
GROUP BY
		Oeuvre.noOeuvre
	,	titre;

[Pop Hip]

Ho nice, j'avais fait la même chose en utilisant un WHERE Emprunt.dateRendu IS NULL et du coup ça ne prenait pas en compte les exemplaire ne se trouvant pas dans Emprunt, mais avec le AND c'est parfait merci beaucoup !