Discussion :

[StringBuilder]

Bonjour,

J'ai une table TABLE1 avec comme clé primaire ID.

J'ai ensuite deux tables TABLE2 et TABLE3 avec comme clé étrangère T1_ID qui pointe sur TABLE1.ID

Je souhaite récupérer en une seule requête par exemple le nombre de lignes de TABLE2 qui référencent une ligne de TABLE1, ainsi que le le nombre de lignes de TABLE3 qui référencent une ligne de TABLE1.

La tête dans le guidon, ça conduit à cette requête :

Code sql :

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

1
2
3
4
5
6
7
 
select table1.id, count(table2.*) nbt2, count(table3.*) nbt3
from table1
inner join table2 on table2.t1_id = table1.id
inner join table3 on table3.t1_id = table1.id
where table1.id = 1
group by table1.id;

Pour une raison X ou Y, je ne peux pas, dans mon cas, faire de count(distinct matable.champunique) car les clés de mes tables TABLE2 et TABLE3 sont composite et il n'y a aucun champ unique dedans.

J'ai alors la solution suivante :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
select tmp.id, tmp.nbt2, count(table3.*) nbt3
from (
  select table1.id, count(table2.*) nbt2
  from table1
  inner join table2 on table2.t1_id = table1.id
  group by table1.id
) tmp
inner join table3 on table3.t1_id = tmp.id
where tmp.id = 1
group by tmp.id;

Ou encore :

Code sql :

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

1
2
3
4
5
6
 
select table1.id, count(table2.*) nbt2, (select count(table3.*) from table3 where table3.id = table1.id) nbt3
from table1
inner join table2 on table2.t1_id = table1.id
where table1.id = 1
group by table1.id;

Mais ni l'une, ni l'autre des deux syntaxe n'est naturelle ni claire (et elles sont assez peu performantes).

Existe-t-il une autre solution ?

[tatayo]

Bonjour,

Pour une raison X ou Y, je ne peux pas, dans mon cas, faire de count(distinct matable.champunique) car les clés de mes tables TABLE2 et TABLE3 sont composite et il n'y a aucun champ unique dedans.

Tu peux faire un count(distinct table2.key1 || '|' || table2.key2). Ainsi tu retrouves une valeur unique pour chaque ligne de la table.

Tatayo.

[StringBuilder]

C'est effectivement ce à quoi j'ai pensé au moment où j'ai écrit ces lignes

En revanche, niveau performances, n'est-ce pas catastrophique ?

[CinePhil]

J'ai une table TABLE1 avec comme clé primaire ID.

J'ai ensuite deux tables TABLE2 et TABLE3 avec comme clé étrangère T1_ID qui pointe sur TABLE1.ID

Je souhaite récupérer en une seule requête par exemple le nombre de lignes de TABLE2 qui référencent une ligne de TABLE1, ainsi que le le nombre de lignes de TABLE3 qui référencent une ligne de TABLE1.

Normalement, une clé étrangère n'est pas nulle donc toutes les lignes de TABLE2 et toutes les lignes de TABLE3 devraient référencer une ligne de TABLE1 !

Mais si tu poses la question, c'est que ce n'est pas le cas et que tu as des clés étrangères à NULL !

nombre de lignes de TABLE2 qui référencent une ligne de TABLE1

Code :

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

1
2
3
SELECT COUNT(*) AS nb_table2
FROM TABLE2
WHERE TABLE1_ID IS NOT NULL

Si tu veux une ligne de résultat pour chaque table, il suffit de faire une union :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT table_origine, nombre_lignes_referencant_table1
FROM
(
	SELECT 'Table 2' AS table_origine, COUNT(*) AS nombre_lignes_referencant_table1
	FROM TABLE2
	WHERE TABLE1_ID IS NOT NULL
	UNION
	SELECT 'Table 3' AS table_origine, COUNT(*) AS nombre_lignes_referencant_table1
	FROM TABLE3
	WHERE TABLE1_ID IS NOT NULL
)

[Waldar]

On fait les agrégats au préalable, et la jointure après :

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
With cpt_t2 as
(
  select t1_id, count(*) as nb_t2
    from table2
group by t1_id
)
  ,  cpt_t3 as
(
  select t1_id, count(*) as nb_t3
    from table3
group by t1_id
)
select t1.id
     , coalesce(t2.nb_t2, 0) as nb_t2
     , coalesce(t3.nb_t3, 0) as nb_t3
  from table1 t1
       left outer join cpt_t2 t2
         on t2.t1_id = t1.id
       left outer join cpt_t3 t3
         on t3.t1_id = t1.id;;

[StringBuilder]

Normalement, une clé étrangère n'est pas nulle donc toutes les lignes de TABLE2 et toutes les lignes de TABLE3 devraient référencer une ligne de TABLE1 !

Mais si tu poses la question, c'est que ce n'est pas le cas et que tu as des clés étrangères à NULL !

Absolument pas.

J'ai juste par exemple TABLE1 = Client
TABLE2 = Commande
TABLE3 = Livraison

Et en une requête, je veux avoir la liste des clients avec le nombre de commandes et de livraisons qu'il a passé.

Il n'est pas question d'avoir des références nulles.

En revanche, TABLE2 et TABLE3 on des clés composites, et donc n'ont pas de champ unique sur lequel je peux faire un count(distinct)

Il reste l'astuce de la concaténation des champs de la clé composite, mais niveau performances je pense que c'est encore pire que les sous-requêtes./

[StringBuilder]

On fait les agrégats au préalable, et la jointure après :

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
With cpt_t2 as
(
  select t1_id, count(*) as nb_t2
    from table2
group by t1_id
)
  ,  cpt_t3 as
(
  select t1_id, count(*) as nb_t3
    from table3
group by t1_id
)
select t1.id
     , coalesce(t2.nb_t2, 0) as nb_t2
     , coalesce(t3.nb_t3, 0) as nb_t3
  from table1 t1
       left outer join cpt_t2 t2
         on t2.t1_id = t1.id
       left outer join cpt_t3 t3
         on t3.t1_id = t1.id;;

En effet, c'est une solution.

En revanche... Niveau performances, est-ce que c'est vraiment mieux que les approches que j'ai indiqué dans mon premier post ?

[CinePhil]

Ta demande n'était pas claire.
Si tu donnais ton cas réel, ce serait plus facile de te répondre efficacement.

En attendant, je prends ton exemple :

J'ai juste par exemple TABLE1 = Client
TABLE2 = Commande
TABLE3 = Livraison

Et en une requête, je veux avoir la liste des clients avec le nombre de commandes et de livraisons qu'il a passé.

Code :

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

1
2
3
4
5
6
7
SELECT clt.clt_id, 
	COUNT(cmd.cmd_id_client) AS nb_commandes,
	COUNT(liv.liv_id_client) AS nb_livraisons
FROM client clt
LEFT OUTER JOIN commande cmd ON cmd.cmd_id_client = clt.clt_id
LEFT OUTER JOIN livraison liv ON liv.liv_id_client = clt.clt_id
GROUP BY clt.clt_id

Ou alors je n'ai toujours pas compris ce que tu veux ?

[StringBuilder]

La ça fait un produit cartésien, c'est ma première requête, tu peux vérifier.

Il faudrait, à condition que les tables commande et livraison aient une clé unique :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT clt.clt_id, 
	COUNT(distinct cmd.cmd_id) AS nb_commandes,
	COUNT(distinct liv.liv_id) AS nb_livraisons
FROM client clt
LEFT OUTER JOIN commande cmd ON cmd.cmd_id_client = clt.clt_id
LEFT OUTER JOIN livraison liv ON liv.liv_id_client = clt.clt_id
GROUP BY clt.clt_id

Mais ce n'est pas le cas dans mon modèle des données.

Aussi, je n'ai pas donné cet exemple "concret" dès le départ tout simplement parce que dans mon cas, il s'agit de tables "DSK" "LSK" et "ESK", représentant respectivement des fiches de stock, des lots et des emplacements, et qu'elles sont liées de la façon suivante : DSK -1----n- LSK -1----n- ESK ce qui est totalement différent du cas concret que j'ai donné.

Au détail près que j'ai le même problème, puisque je veux comparer la valeur C01 de DSK avec la somme des C01 de LSK pour le même couple produit/dépôt, avec la somme des C01 de ESK pour le même couple produit/dépôt.

[CinePhil]

Trop complexe à imaginer sans structure réelle des tables, exemple de données et résultat attendu.

[StringBuilder]

Bon, tu l'auras voulu :o

DSK
CODSOC
SIGDEP
CODPRO
C01

LSK
CODSOC
SIGDEP
CODPRO
NUMLOT
C01

ESK
CODSOC
SIGDEP
CODPRO
NUMLOT
CODEMP
C01

Nous sommes dans la société 100, dépôt DEP1 et produit PRO001.

Il y a 10 unités de stock :
DSK

Code :

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

1
2
3
 
CODSOC SIGDEP CODPRO C01
   100 DEP1   PRO001  10

Ces 10 unités sont éclatées en trois lots :

Code :

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

1
2
3
4
5
 
CODSOC SIGDEP CODPRO NUMLOT C01
   100 DEP1   PRO001      1   5
   100 DEP1   PRO001      2   2
   100 DEP1   PRO001      3   3

Qui sont répartis dans plusieurs emplacements :

Code :

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

1
2
3
4
5
6
7
8
 
CODSOC SIGDEP CODPRO NUMLOT CODEMP C01
   100 DEP1   PRO001      1 EMP001   1
   100 DEP1   PRO001      1 EMP002   2
   100 DEP1   PRO001      1 EMP003   2
   100 DEP1   PRO001      2 EMP001   1
   100 DEP1   PRO001      2 EMP002   1
   100 DEP1   PRO001      3 EMP004   3

Je veux vérifier que DSK.C01 = SUM(LSK.C01) = SUM(ESK.C01)

En effet, une erreur avec une interface fait qu'il y a des déséquilibres entre les 3 tables (ce qui est normalement impossible)
J'ai donc dû faire une extraction des 3 tables pour toutes les fiches de stock de ma société 100 où il y a un déséquilibre entre les 3 sommes.

Tu verras qu'avec ta requête, qui est effectivement celle que j'ai essayé en premier, ça fait un produit cartésien entre les quantités de ESK et LSK.

Après, inutile de faire des commentaires sur le fait que DSK et LSK peuvent être calculées à partir de ESK : le modèle est comme ça, j'y suis pour rien, et d'un point de vue performances, ESK est bien trop grosse pour se permettre de vérifier les compteurs de stock dedans dans les fonctions inter-actives de saisie de commande par exemple.

[CinePhil]

Je veux vérifier que DSK.C01 = SUM(LSK.C01) = SUM(ESK.C01)

Est-ce que cette requête répond au besoin ?

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
SELECT d.CODSOC, d.SIGDEP, d.CODPRO,
	d.C01 AS qte_en_stock,
	SUM(l.C01) AS somme_des_lots,
	SUM(e.C01) AS somme_des_emplacements
FROM DSK d
INNER JOIN LSK l 
	ON l.CODSOC = d.CODSOC
	AND l.SIGDEP = d.SIGDEP
	AND l.CODPRO = d.CODPRO
 
	INNER JOIN ESK e 
		ON e.CODSOC = l.CODSOC
		AND e.SIGDEP = l.SIGDEP
		AND e.CODPRO = l.CODPRO
		AND e.NUMLOT = l.NUMLOT
GROUP BY d.CODSOC, d.SIGDEP, d.CODPRO
HAVING d.C01 <> SUM(l.C01)
	OR d.C01 <> SUM(e.C01)
	OR SUM(l.C01) <> SUM(e.C01)

[StringBuilder]

Ben pour le coup, je reste sur mon derrière.

Ça marche !

Par contre, je vois pas comment c'est possible, puisque quand on enlève les SUM et le group by, on voit bien que les C01 de DSK et LSK sont démultipliés...

[StringBuilder]

Ah, non, je me disais aussi... J'ai testé sur la base de DEV, dans laquel il y a toujours une ligne dans ESK pour chaque LSK... Mais ca marche pas en prod, où il y a de vraies données dans ESK

[CinePhil]

Ah, non, je me disais aussi... J'ai testé sur la base de DEV, dans laquel il y a toujours une ligne dans ESK pour chaque LSK... Mais ca marche pas en prod, où il y a de vraies données dans ESK

Tu veux dire que dans les vraies données il y a des lots sans emplacement ?

Jointures externes alors.

EDIT :

Par contre, je vois pas comment c'est possible, puisque quand on enlève les SUM et le group by, on voit bien que les C01 de DSK et LSK sont démultipliés...

DSK.C01 est brut dans ma requête, pas additionné, donc il n'est compté qu'une fois. D'ailleurs, je pense qu'en toute rigueur cette colonne devrait figurer dans le GROUP BY puisqu'il n'y a pas de fonction d'agrégation dessus, ce que je n'ai pas fait.

Par contre, c'est vrai que si un lot à plusieurs emplacements, la somme des lots va donner en fait la somme des {lot, emplacement} donc une quantité trop grande pour les lots.

Je vais revoir ma requête...

EDIT 2 :
Essaie avec cette requête :

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
SELECT d.CODSOC, d.SIGDEP, d.CODPRO,
	d.C01 AS qte_en_stock,
	l.somme_des_lots,
	SUM(e.C01) AS somme_des_emplacements
FROM DSK d
LEFT OUTER JOIN 
(
	SELECT CODSOC, SIGDEP, CODPRO,
		SUM(C01) AS somme_des_lots
	FROM LSK
	GROUP BY CODSOC, SIGDEP, CODPRO
) l 
	ON l.CODSOC = d.CODSOC
	AND l.SIGDEP = d.SIGDEP
	AND l.CODPRO = d.CODPRO
	AND d.C01 <> l.somme_des_lots
 
LEFT OUTER JOIN ESK e 
	ON e.CODSOC = l.CODSOC
	AND e.SIGDEP = l.SIGDEP
	AND e.CODPRO = l.CODPRO
GROUP BY d.CODSOC, d.SIGDEP, d.CODPRO, d.C01, l.somme_des_lots
HAVING d.C01 <> SUM(e.C01)
	OR l.somme_des_lots <> SUM(e.C01)

EDIT 3 :
Correction de la requête avec jointures externes

[Waldar]

En revanche... Niveau performances, est-ce que c'est vraiment mieux que les approches que j'ai indiqué dans mon premier post ?

Ça dépend. Le fait de construire les agrégats au préalable fait que vous ne pourrez pas descendre une liste d'id sauf si vous la spécifiez.

Pour sur l'ensemble des données, je pense que c'est le plus rapide.

[StringBuilder]

Tu veux dire que dans les vraies données il y a des lots sans emplacement ?

Non non. Enfin, dans ce cas, un emplacement bidon est créé dans ESK, donc les jointures INNER fonctionne toujours.

EDIT :

DSK.C01 est brut dans ma requête, pas additionné, donc il n'est compté qu'une fois. D'ailleurs, je pense qu'en toute rigueur cette colonne devrait figurer dans le GROUP BY puisqu'il n'y a pas de fonction d'agrégation dessus, ce que je n'ai pas fait.

Oui, sous Oracle, ça passe pas si on le met pas dans le GROUP BY de toute façon

Par contre, c'est vrai que si un lot à plusieurs emplacements, la somme des lots va donner en fait la somme des {lot, emplacement} donc une quantité trop grande pour les lots.

Je vais revoir ma requête...

C'est exactement ça

EDIT 2 :
Essaie avec cette requête :

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
SELECT d.CODSOC, d.SIGDEP, d.CODPRO,
	d.C01 AS qte_en_stock,
	l.somme_des_lots,
	SUM(e.C01) AS somme_des_emplacements
FROM DSK d
INNER JOIN 
(
	SELECT CODSOC, SIGDEP, CODPRO,
		SUM(C01) AS somme_des_lots
	FROM LSK
	GROUP BY CODSOC, SIGDEP, CODPRO
) l 
	ON l.CODSOC = d.CODSOC
	AND l.SIGDEP = d.SIGDEP
	AND l.CODPRO = d.CODPRO
 
INNER JOIN ESK e 
	ON e.CODSOC = l.CODSOC
	AND e.SIGDEP = l.SIGDEP
	AND e.CODPRO = l.CODPRO
GROUP BY d.CODSOC, d.SIGDEP, d.CODPRO, d.C01, l.somme_des_lots
HAVING d.C01 <> l.somme_des_lots
	OR d.C01 <> l.somme_des_lots
	OR l.somme_des_lots <> SUM(e.C01)

Oui mais ça, c'est ma seconde requête, que je trouve assez peu lisible, et je demandais s'il y avait une solution plus propre

[CinePhil]

Ben je ne pense pas qu'il y a ait plus simple non.
Et je ne la trouve pas sale ma requête !