Discussion :

[jportemer]

Bonjour à tous !

Je me lance dans la modélisation puis la gestion d'une BDD se branchant sur une mini-application web de comptes financiers maison.
L'idée est de pouvoir historiser tous les mouvement d'argents (débit/crédit) depuis et vers les comptes en banque de plusieurs personnes.
J'utilise SQLite3.

J'ai simplifié le problème en gardant 3 tables de mon modèle.
-PROPRIETAIRE
-COMPTE : un propriétaire détient 1 ou plusieurs comptes, il y a donc une clé étrangère sur l'id de la table PROPRIETAIRE
-ENREGISTREMENT, qui historise les flux financiers et leurs montants. Il est caractérisé par un compte débiteur et un compte créditeur, qui constituent deux clés étrangères sur la table COMPTE.
Difficulté supplémentaire : la table enregistre également les flux depuis ou vers l'extérieur, qui n'est pas un compte. Danc ce cas la clé correspondante est NULL. J'ai donc trois cas :

• Virement direct d'un compte A (id_debiteur) à un compte B (id_crediteur), tous deux référencés dans la base
• Dépense : d'un compte A (id_debiteur) vers l'extérieur (id_crediteur = NULL)
• Réception d'argent de l'extérieur (id_debiteur = NULL) vers un compte B (id_crediteur)

Modèle :

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
CREATE TABLE PROPRIETAIRE(
	id INTEGER PRIMARY KEY,
	nom TEXT NOT NULL
);
 
CREATE TABLE COMPTE(
	id INTEGER PRIMARY KEY,
	nom TEXT,
	id_proprietaire INT NOT NULL,
 
	FOREIGN KEY(id_proprietaire) REFERENCES PROPRIETAIRE(id)
);
 
CREATE TABLE ENREGISTREMENT(
	id INTEGER PRIMARY KEY,
	id_compte_debiteur INT, -- NULL si extérieur
	id_compte_crediteur INT, -- NULL si extérieur
	commentaire TEXT,
	montant INT, -- En centimes, donc entier
 
	FOREIGN KEY(id_compte_debiteur) REFERENCES COMPTE(id),
	FOREIGN KEY(id_compte_crediteur) REFERENCES COMPTE(id)
);

Valeurs pour l'exemple, avec deux quidams Bibi et Bobo :

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 PROPRIETAIRE VALUES (1, "Bibi");
INSERT INTO PROPRIETAIRE VALUES (2, "Bobo");
 
INSERT INTO COMPTE VALUES (1, "Compte 1 de Bibi", 1);
INSERT INTO COMPTE VALUES (2, "Compte 2 de Bibi", 1);
INSERT INTO COMPTE VALUES (3, "Compte 1 de Bobo", 2);
INSERT INTO COMPTE VALUES (4, "Compte 2 de Bobo", 2);
 
INSERT INTO ENREGISTREMENT VALUES (1, 1, 2, "Bibi C1 -> Bibi C2", 2000);
INSERT INTO ENREGISTREMENT VALUES (2, 3, 4, "Bobo C1 -> Bobo C1", 3000);
INSERT INTO ENREGISTREMENT VALUES (3, 1, 4, "Bibi C1 -> Bobo C2", 100);
INSERT INTO ENREGISTREMENT VALUES (4, 2, NULL, "Bibi C2 -> Exterieur", 5300);
INSERT INTO ENREGISTREMENT VALUES (5, 3, NULL, "Bobo C1 -> Exterieur", 10000);
INSERT INTO ENREGISTREMENT VALUES (6, NULL, 1, "Exterieur -> Bibi C1", 600);
 
SELECT * FROM ENREGISTREMENT; (je fais apparaître les noms de colonnes et les NULL pour que ce soit plus clair)
id | id_compte_debiteur | id compte crediteur |     commentaire        | montant
__________________________________________________________________________________
 1 |         1          |          2          | "Bibi C1 -> Bibi C1"   | 2000               
 2 |         3          |          4          | "Bobo C1 -> Bobo C2"   | 3000               
 3 |         1          |          4          | "Bibi C1 -> Bobo C2"   | 100              
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 5 |         3          |        NULL         | "Bobo C1 -> Exterieur" | 10000             
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600

Mon problème est le suivant : Comment récupérer tous les enregistrements qui concernent Bibi, c'est à dire pour lequel Bibi est soit le débiteur soit le créditeur ?

Résultat désiré, les enregistrements 1, 3, 4 et 6 :

Code :

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

1
2
3
4
5
6
id | id_compte_debiteur | id compte crediteur |     commentaire        | montant
__________________________________________________________________________________
 1 |         1          |          2          | "Bibi C1 -> Bibi C2"   | 2000               (Bibi est à la fois débiteur et créditeur)
 3 |         1          |          4          | "Bibi C1 -> Bobo C2"   | 100                (Bibi est débiteur)
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300               (Bibi est débiteur)
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600                (Bibi est créditeur)

Je cherche à appliquer le raisonnement suivant : partant de la table ENREGISTREMENT, je remonte deux fois à la table COMPTE, une fois en tant que débiteur et une autre en tant que créditeur, via les clés étrangères puis j'utilise le second lien pour remonter à table PROPRIETAIRE, une fois en tant que débiteur et une autre en tant que créditeur, afin de récupérer le nom.
Essai :

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
32
33
34
35
36
37
38
39
40
41
42
SELECT ENREGISTREMENT.* 
FROM
	ENREGISTREMENT,
	COMPTE AS compte_debiteur,
	COMPTE AS compte_crediteur,
	PROPRIETAIRE AS debiteur,
	PROPRIETAIRE AS crediteur
WHERE (id_compte_debiteur IS NULL OR ( -- Ne faire les jointures que si l'id n'est pas NULL
	id_compte_debiteur = compte_debiteur.id
	AND compte_debiteur.id_proprietaire = debiteur.id
))
AND (id_compte_crediteur IS NULL OR ( -- Ne faire les jointures que si l'id n'est pas NULL
	id_compte_crediteur = compte_crediteur.id
	AND compte_crediteur.id_proprietaire = crediteur.id
))
AND (debiteur.nom = "Bibi" OR crediteur.nom = "Bibi");
 
id | id_compte_debiteur | id compte crediteur |     commentaire        | montant
__________________________________________________________________________________
 1 |         1          |          2          | "Bibi C1 -> Bibi C1"   | 2000
 2 |         3          |          4          | "Bobo C1 -> Bobo C2"   | 3000                
 3 |         1          |          4          | "Bibi C1 -> Bobo C2"   | 100              
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 4 |         2          |        NULL         | "Bibi C2 -> Exterieur" | 5300
 5 |         3          |        NULL         | "Bobo C1 -> Exterieur" | 10000 
 5 |         3          |        NULL         | "Bobo C1 -> Exterieur" | 10000
 5 |         3          |        NULL         | "Bobo C1 -> Exterieur" | 10000 
 5 |         3          |        NULL         | "Bobo C1 -> Exterieur" | 10000        
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600              
 6 |        NULL        |          1          | "Exterieur -> Bibi C1" | 600

Comme vous pous pouvez le constater, ça ne fonctionne pas... J'obtiens TOUS les enregistrements de la table ENREGISTREMENT et en plusieurs exemplaires !

Si vous avez ne serait-ce qu'un début de piste, je suis preneur

Merci beaucoup !

Bonne soirée !

[acaumes]

Bonjour,

Dans ta requête tu mélanges les conditions sur les clés étrangères que tu dois utiliser avec tes jointures avec les restrictions sur les résultats dans la clause WHERE.

Tu devrais donc utiliser les jointures normalisées qui en plus te permettrons de traiter les cas de virements extérieurs de manière simple.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT ENREGISTREMENT.*
FROM
	ENREGISTREMENT
        left join compte compte_debiteur on ENREGISTREMENT.id_compte_debiteur = compte_debiteur.id      -- Jointure gauche sur les comptes pour le débiteur
        left join proprietaire debiteur on compte_debiteur.id_proprietaire = debiteur.id                                -- Jointure gauche sur les propriétaires pour le débiteur
        left join compte compte_crediteur on ENREGISTREMENT.id_compte_crediteur = compte_crediteur.id   -- Jointure gauche sur les comptes pour le créditeur
        left join proprietaire crediteur on compte_crediteur.id_proprietaire = crediteur.id                             -- Jointure gauche sur les propriétaires pour le créditeur
 
 
WHERE
 (debiteur.nom = "Bibi" OR crediteur.nom = "Bibi")  -- Restriction sur le propriétaire intéressé

Cordialement

[jportemer]

Code :

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

1
2
3
4
5
 
        left join compte compte_debiteur on ENREGISTREMENT.id_compte_debiteur = compte_debiteur.id      -- Jointure gauche sur les comptes pour le débiteur
        left join proprietaire debiteur on compte_debiteur.id_proprietaire = debiteur.id                                -- Jointure gauche sur les propriétaires pour le débiteur
        left join compte compte_crediteur on ENREGISTREMENT.id_compte_crediteur = compte_crediteur.id   -- Jointure gauche sur les comptes pour le créditeur
        left join proprietaire crediteur on compte_crediteur.id_proprietaire = crediteur.id                             -- Jointure gauche sur les propriétaires pour le créditeur

Merci. Par contre il ne manque pas un AS à chaque fois ? (entre compte et compte_debiteur, etc.).
J'avais essayé qu'avec JOIN tout court, mais pas LEFT JOIN . J'essaie tout de suite !

[acaumes]

Bonjour,

On peut ou pas le mettre, cela ne change rien (voir la syntaxe de SQLITE). J'ai personnellement l’habitude de l'utiliser dans les alias de colonnes mais pas dans les alias de tables pour les jointures.

Cordialement

[jportemer]

Effectivement dans la syntaxe, le AS est facultatif.
Merci beaucoup, ta solution fonctionne bien dans mon cas ! Ça m'a débloqué, vraiment.
Je passe le topic en résolu.

Merci encore et bonne soirée.

Jean