Discussion :

[Nerva]

Bonjour.

Je bute sur une requête ; j'ai essayé plusieurs approches (sous-requête, MAX, CASE, jointure) mais sans résultat...

Une personne peut avoir 2 adresses : adresse personnelle et adresse de correspondance. Une personne qui possède 2 adresses est donc référencée 2 fois dans la table des adresses.
Lorsqu'on établit une facture, il faut que ce soit l'adresse de correspondance qui soit sélectionnée si elle existe ; si elle n'existe pas, c'est l'adresse personnelle.

Définition 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
CREATE TABLE T_PERSONNES (
	PER_ID INTEGER NOT NULL,
	PER_NOM VARCHAR (32) NOT NULL,
	PER_PRENOM VARCHAR (32) NOT NULL,
	PER_COR TINYINT DEFAULT 0 NOT NULL,
	CONSTRAINT PK_PER_ID PRIMARY KEY (PER_ID),
	CONSTRAINT CC_PER_COR CHECK (PER_COR BETWEEN 0 AND 1)
);
 
-- PER_COR : définit si oui (1) ou non (0) la personne est sous tutorat, et donc possède une adresse de correspondance.
 
CREATE TABLE TR_ADRESSES (
	ADT_ID INTEGER NOT NULL,
	ADT_LIB VARCHAR (24) NOT NULL,
	CONSTRAINT PK_ADT_ID PRIMARY KEY (ADT_ID)
);
 
-- ADT_ID : (1) adresse personnelle ; (2) adresse de correspondance.
-- ADT_LIB : intitulés.
 
CREATE TABLE T_ADRESSES (
	ADR_ID INTEGER NOT NULL,
	ADT_ID INTEGER NOT NULL,
	PER_ID INTEGER NOT NULL,
	ADR_L1 VARCHAR (32) NOT NULL,
	CONSTRAINT PK_ADR_ID PRIMARY KEY (ADR_ID),
	CONSTRAINT FK_ADR_PER FOREIGN KEY (PER_ID) REFERENCES T_PERSONNES (PER_ID),
	CONSTRAINT FK_ADR_ADT FOREIGN KEY (ADT_ID) REFERENCES TR_ADRESSES (ADT_ID)
);

Vous en remerciant.

[vanagreg]

Bonjour,

Quel est ton SGBDR?
De quelles infos tu as besoin dans la requête?
Tu pourrais par exemple utiliser la fonction ROW_NUMBER pour choisir la bonne adresse en triant par le type d'adresse voulu.

[Nerva]

1) HSQLDB intégré à LibreOffice Base.

2) Dans la requête, j'aurais déjà besoin pour voir si ça fonctionne, d'un listing avec les nom, prénom et adresse des personnes, mais pas de lignes en double pour celles qui ont deux adresses, juste celle avec l'adresse de correspondance. Par exemple, avec ce jeu de données, Durant et Duval ont une adresse de correspondance.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO T_PERSONNES (PER_ID, PER_NOM, PER_PRENOM, PER_COR) VALUES (1, 'Dupont', 'Jeanne', 0);
INSERT INTO T_PERSONNES (PER_ID, PER_NOM, PER_PRENOM, PER_COR) VALUES (2, 'Durant', 'Sarah', 1);
INSERT INTO T_PERSONNES (PER_ID, PER_NOM, PER_PRENOM, PER_COR) VALUES (3, 'Duval', 'Carole', 1);
INSERT INTO T_PERSONNES (PER_ID, PER_NOM, PER_PRENOM, PER_COR) VALUES (4, 'Dufour', 'Axelle', 0);
INSERT INTO T_PERSONNES (PER_ID, PER_NOM, PER_PRENOM, PER_COR) VALUES (5, 'Dupuis', 'Pierre', 0);
 
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (1, 1, 1, '124 place Hildegard von Bingen');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (2, 1, 2, '55 rue Bernart de Ventadour');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (3, 2, 2, '172 rue Karl Friedrich Abel');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (4, 1, 3, '22 résidence Pierre Beaulieu');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (5, 2, 3, '27 rue François André Philidor');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (6, 1, 4, '41 rue Alphonse de Castille');
INSERT INTO T_ADRESSES (ADR_ID, ADT_ID, PER_ID, ADR_L1) VALUES (7, 1, 5, '176 rue Adam De La Halle');

Résultat de la requête :

Code :

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

1
2
3
4
5
6
7
PER_NOM		PER_PRENOM		ADR_L1
--------------------------------------------------------------
Dupont		Jeanne			124 place Hildegard von Bingen
Durant		Sarah			172 rue Karl Friedrich Abel
Duval		Carole			27 rue François André Philidor
Dufour		Axelle			41 rue Alphonse de Castille
Dupuis		Pierre			176 rue Adam De La Halle

3) Je ne connais pas la fonction ROW_NUMBER.

[Séb.]

Tu peux utiliser des jointures externes :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT ALL
    TP.PER_ID, TP.PER_NOM, TP.PER_PRENOM,
    COALESCE(TA1.ADR_L1, TA2.ADR_L1, '--') AS FACT_ADR
FROM T_PERSONNES AS TP
LEFT OUTER JOIN T_ADRESSES AS TA1 ON TP.PER_ID = TA1.PER_ID AND TA1.ADT_ID = 1 
LEFT OUTER JOIN T_ADRESSES AS TA2 ON TP.PER_ID = TA2.PER_ID AND TA2.ADT_ID = 2
WHERE TP.PER_ID = :id
;

Ou des sous-requêtes :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
SELECT ALL
    TP.PER_ID, TP.PER_NOM, TP.PER_PRENOM,
    COALESCE(
        (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = TP.PER_ID AND ADT_ID = 1),
        (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = TP.PER_ID AND ADT_ID = 2),
        '--'
     ) AS FACT_ADR
FROM T_PERSONNES AS TP
WHERE TP.PER_ID = :id
;

[escartefigue]

Bonjour,

Dans votre script, il manque les insertions dans la table des types d'adresses.
Une fois ce script complété, on peut proposer cette autre solution qui recherche le type d'adresse le plus haut parmi les types 1 et 2 :

Code SQL :

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 PE.PER_NOM    as "Nom"
     , PE.PER_PRENOM as "Prénom"
     , AD.ADR_L1     as "Adresse"
from T_PERSONNES      as PE
inner join T_ADRESSES as AD
    on AD.PER_ID = PE.PER_ID
   and AD.ADT_ID in (1, 2)
where AD.ADT_ID =
     (select max(ADT_ID)
      from T_ADRESSES as SUBQ
      where SUBQ.PER_ID = PE.PER_ID
        and SUBQ.ADT_ID in (1, 2)
     )
order by PE.PER_NOM
       , PE.PER_PRENOM

Ici j'ai ajouté une restriction sur les types 1 et 2 au cas où d'autres types d'adresses seraient ajoutés.

La solution basée sur la fonction ROW_NUMBER() me semble peu intéressante ici dans la mesure où l'on a déjà le type adresse qui permet de savoir laquelle retenir.
On peut également remplacer la fonction max() ci-dessus par un test d'inexistence (NOT EXISTS) d'une adresse d'un type supérieur pour la personne.
Bref, comme souvent, de nombreuses solutions sont possibles


Note je ne sais pas ce qu'il en est dans LibreOffice Base, mais dans la plupart des SGBD, il n'est pas nécessaire de répéter l'ordre INSERT à chaque ligne, on peut se simplifier la vie en écrivant les insertions ainsi :

Code SQL :

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

1
2
3
4
insert into TR_ADRESSES(ADT_ID, ADT_LIB)
values (1, 'perso')
     , (2, 'corresp')
;

[Nerva]

Je réponds pour commencer à Seb

Déjà merci. J'ai supprimé le paramètre WHERE T_PERSONNES.PER_ID = :id (dont je ne comprends pas l'utilité puisqu'il ne sélectionne qu'une personne) et j'ai inversé les 2 lignes SELECT car en l'état, c'est l'adresse personnelle qui était sélectionnée pour tout le monde. Donc, en épurant :

Code :

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

1
2
3
4
5
6
7
8
SELECT ALL
    PER_ID, PER_NOM, PER_PRENOM,
    COALESCE(
        (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 2),
        (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 1)
     ) AS ADR_L1
FROM T_PERSONNES
;

À quoi sert le '--' ? Je l'ai supprimé et ça fonctionne toujours.

Avant d'aller plus loin, j'ai une question. Sachant que ADT_ID, qui définit le type d'adresse, figure dans T_ADRESSES, y aurait-il un moyen d'obtenir la même chose mais sans incorporer la table T_PERSONNES ? Si je demande ça c'est parce que j'ai simplifié l'énoncé pour ce sujet. Dans la finalité, c'est comme ceci :



La table des adresses (ainsi que celle des localités) est utilisée dans plusieurs requêtes et je me demande si il n'y a pas des risques d'embrouilles. Pour le moment, j'ai complété ainsi afin d'intégrer les titre de courtoisie, complément d'adresse, code postal et localité :

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
SELECT ALL
PER_ID,
TIT_LIB,
PER_NOM,
PER_PRENOM,
 
COALESCE(
    (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 2),
    (SELECT ALL ADR_L1 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 1)
) AS ADR_L1,
 
COALESCE(
    (SELECT ALL ADR_L2 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 2),
    (SELECT ALL ADR_L2 FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 1)
) AS ADR_L2,
 
COALESCE(
    (SELECT ALL LOC_ID FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 2),
    (SELECT ALL LOC_ID FROM T_ADRESSES WHERE PER_ID = T_PERSONNES.PER_ID AND ADT_ID = 1)
 ) AS LOC_ID,
 
LOC_CP,
LOC_LIB
 
FROM T_PERSONNES
RIGHT OUTER JOIN TR_TITRES ON T_PERSONNES.TIT_ID = TR_TITRES.TIT_ID
RIGHT OUTER JOIN T_ADRESSES ON T_PERSONNES.PER_ID = T_ADRESSES.PER_ID
RIGHT OUTER JOIN T_LOCALITES ON T_ADRESSES.LOC_ID = T_LOCALITES.LOC_ID

Mais je m'embrouille avec les jointures : dans l'état actuel, toutes les lignes d'adresses sont affichées...

[Nerva]

@escartefigue

Merci aussi, ça fonctionne. Cette formulation m'est d'ailleurs plus familière.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
PER_NOM,
PER_PRENOM,
ADR_L1
FROM T_PERSONNES      
INNER JOIN T_ADRESSES ON T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
WHERE ADT_ID =
     (SELECT MAX(ADT_ID)
      FROM T_ADRESSES
      WHERE T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
     )
ORDER BY PER_NOM, PER_PRENOM

Edit : en intégrant les autres tables, c'est parfait :

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
SELECT
TIT_LIB,
PER_NOM,
PER_PRENOM,
ADT_ID,
ADR_L1,
ADR_L2,
LOC_CP,
LOC_LIB
FROM T_PERSONNES
INNER JOIN TR_TITRES ON T_PERSONNES.TIT_ID = TR_TITRES.TIT_ID   
INNER JOIN T_ADRESSES ON T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
INNER JOIN T_LOCALITES ON T_ADRESSES.LOC_ID = T_LOCALITES.LOC_ID
WHERE ADT_ID =
     (SELECT MAX(ADT_ID)
      FROM T_ADRESSES
      WHERE T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
     )
 
ORDER BY PER_NOM

[Nerva]

Voici la requête la plus complète que j'ai à exécuter :

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
43
44
45
46
47
48
SELECT
FAC_ID,
FAC_DATE,
YEAR(FAC_DATE_DEB) AS FAC_ANNEE,
MONTH(FAC_DATE_DEB) AS FAC_MOIS,
PER_ID,
TIT_LIB,
PER_NOM,
PER_PRENOM,
ADR_L1,
ADR_L2,
LOC_CP,
LOC_LIB,
SEC_LIB,
PRO_LIB,
FAC_PUTTC,
SUM(FAC_QTE) AS FAC_QTE,
SUM(FAC_PUTTC * FAC_QTE) AS FAC_TTTC
FROM T_PERSONNES
INNER JOIN TR_TITRES ON T_PERSONNES.TIT_ID = TR_TITRES.TIT_ID   
INNER JOIN T_ADRESSES ON T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
INNER JOIN T_LOCALITES ON T_ADRESSES.LOC_ID = T_LOCALITES.LOC_ID
INNER JOIN T_SECTEURS ON T_PERSONNES.SEC_ID = T_SECTEURS.SEC_ID
INNER JOIN T_FACTURES ON T_PERSONNES.PER_ID = T_FACTURES.PER_ID
INNER JOIN T_FACTURES_SUB ON T_FACTURES.FAC_ID = T_FACTURES_SUB.FAC_ID
INNER JOIN T_PRODUITS ON T_FACTURES_SUB.PRO_ID = T_PRODUITS.PRO_ID
WHERE ADT_ID =
	(SELECT MAX(ADT_ID)
	FROM T_ADRESSES
	WHERE T_ADRESSES.PER_ID = T_PERSONNES.PER_ID AND T_ADRESSES.ADT_ID IN (1, 2)
	)
	AND FAC_STT = 1
GROUP BY
FAC_ID,
FAC_DATE,
YEAR(FAC_DATE_DEB),
MONTH(FAC_DATE_DEB),
PER_ID,
TIT_LIB,
PER_NOM,
PER_PRENOM,
ADR_L1,
ADR_L2,
LOC_CP,
LOC_LIB,
SEC_LIB,
PRO_LIB,
FAC_PUTTC

Elle semble fonctionner sans problème...