LES JOINTURES SQL

Version imprimable

LES JOINTURES SQL

Bonsoir le forum j'ai un éternel problème voilà j'ai deux tables

Code:

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
49
50
51
52
CREATE TABLE [dbo].[Manquantpompisteparstatioon](
	[IdManquantpompist] [int] NOT NULL,
	[Dates] [date] NULL,
	[IDCLIENT] [nvarchar](100) NULL,
	[EM_ID] [int] NULL,
	[IDCOMPTACLIENT] [int] NULL,
	[Libelle] [nvarchar](250) NULL,
	[Montant] [decimal](18, 3) NULL,
PRIMARY KEY CLUSTERED 
(
	[IdManquantpompist] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon]  WITH CHECK ADD  CONSTRAINT [FK_Manquantpompisteparstatioon_CLIENT] FOREIGN KEY([IDCLIENT], [IDCOMPTACLIENT])
REFERENCES [dbo].[CLIENT] ([IDCLIENT], [IDCOMPTACLIENT])
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon] CHECK CONSTRAINT [FK_Manquantpompisteparstatioon_CLIENT]
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon]  WITH CHECK ADD  CONSTRAINT [FK_Manquantpompisteparstatioon_Employees] FOREIGN KEY([EM_ID])
REFERENCES [dbo].[Employees] ([EM_ID])
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon] CHECK CONSTRAINT [FK_Manquantpompisteparstatioon_Employees]
GO
 
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Depotsparstation](
	[IdDepots] [int] NOT NULL,
	[Dates] [date] NULL,
	[IDCLIENT] [nvarchar](100) NULL,
	[IDCOMPTACLIENT] [int] NULL,
	[Libelle] [nvarchar](250) NULL,
	[Montant] [decimal](18, 3) NULL,
PRIMARY KEY CLUSTERED 
(
	[IdDepots] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Depotsparstation]  WITH CHECK ADD  CONSTRAINT [FK_Depotsparstation_CLIENT] FOREIGN KEY([IDCLIENT], [IDCOMPTACLIENT])
REFERENCES [dbo].[CLIENT] ([IDCLIENT], [IDCOMPTACLIENT])
GO
 
ALTER TABLE [dbo].[Depotsparstation] CHECK CONSTRAINT [FK_Depotsparstation_CLIENT]
GO

j'ai écrit cette requête pour la somme des montants

Code:

1
2
3
4
5
6
7
8
select  d.Dates, c.NOM_CLIENT,sum(D.Montant) as Depots,sum(M.Montant) as ManquantPompiste
from 
 [dbo].[Depotsparstation] D 
full join [dbo].[Manquantpompisteparstatioon] M on M.IDCLIENT=d.IDCLIENT and m.Dates=d.Dates 
inner join [dbo].[CLIENT] c on c.IDCLIENT=d.IDCLIENT
where  d.[IDCLIENT]='cl17' 
group by d.Dates, c.NOM_CLIENT,(D.Montant) ,(M.Montant) 
order by d.Dates asc

mais à chaque insertion de données dans l'une ou l'autre table, le résultat se trouve multiplier soit par 2 par 3 et ainsi de suite et je ne comprend vraiment pas pourquoi quel qu'un pourrai m'aider à résoudre se problème?

Bonjour,

Le besoin n'est pas très clair.

il semble que vous vouliez rapprocher des choses qui n'ont rien à voir entre elles et le critère de jointure est une FK qui plus est nullable

En plus la modélisation est une véritable hérésie, avoir choisi une colonne nvarchar comme identifiant c'est ce qu'on peut faire de pire, toutes les colonnes sont nullables, les noms des tables et des colonnes sont à rallonge et inadaptés, le nom du client est présent dans une table alors que l'identifiant client y est FK, bref tout est à revoir :aie:

Et pour finir vous groupez sur le montant cumulé :weird::aie:

Quoi qu'il en soit, pour ne pas cumuler deux fois les montants, il faut calculer le montant cumulé de l'une des deux tables via une table dérivée.

voici un exemple avec des CTE

Code:

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
 
-- création de la 1ère table "manquant"
with TabMnq (Mid, Mmt, MFK) as
    (select 001,  100.00, 'Durand'       
     union all
     select 002,  150.00, 'Dupont'       
     union all
     select 003,   22.00, 'Martin'       
     union all
     select 004,  110.00, 'Durand'       
     union all
     select 005,   88.00, 'Zorglub'      
     union all
     select 006,  130.00, 'Martin'       
     )
-- création de la 2ème table "dépôt"
   , TabDep (Did, Ddt, Dmt, DFK) as
    (select 001, '2018-01-03', 123.00, 'Durand'  
     union all 
     select 002, '2018-01-03', 456.00, 'AZERTY'  
     union all
     select 003, '2018-01-05', 100.00, 'AZERTY'  
     union all
     select 004, '2018-01-10', 250.00, 'Zorglub' 
     union all
     select 005, '2018-01-12', 040.00, 'Martin'  
    )
-- calcul du cumul par date
   , TabCum (XFK, XDT, XMT) as
      (select DFK
            , Ddt    
            , sum(Dmt) 
     from TabDep 
     group by DFK, Ddt)
-- rapprochement de ce cumul avec le cumul montant, et rapprochement par FK client
select MFK, Xdt, Sum(Mmt), Xmt
from TabMnq M
full outer join
     TabCum X
  on XFK=MFK  
group by MFK, XDT, XMT

Résultat obtenu :

Code:

1
2
3
4
5
6
Dupont	<null>	150	<null>
Durand	2018-01-03	210	123
Martin	2018-01-12	152	40
Zorglub	2018-01-10	88	250
<null>	2018-01-03	<null>	456
<null>	2018-01-05	<null>	100

27/12/2018, 11h40
Massigne

Bonjour escartefigue et merci pour votre réponse sa marche merci:P
27/12/2018, 13h49
escartefigue

Que ça fonctionne, sans doute, mais que le résultat ait un sens fonctionnel, j'en doute encore au vu des critères de jointures...

1 pièce(s) jointe(s)

Bonsoir escartefigue j'ai revue mes tables

Code:

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
49
50
CREATE TABLE [dbo].[Manquantpompisteparstatioon](
	[IdManquantpompist] [int] NOT NULL,
	[Dates] [date] NULL,
	[IDCLIENT] [bigint] NOT NULL,
	[EM_ID] [int] NULL,
	[IDCOMPTACLIENT] [int] NULL,
	[Libelle] [nvarchar](250) NULL,
	[Montant] [decimal](18, 3) NULL,
PRIMARY KEY CLUSTERED 
(
	[IdManquantpompist] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon]  WITH CHECK ADD  CONSTRAINT [FK_Manquantpompisteparstatioon_CLIENTS] FOREIGN KEY([IDCLIENT])
REFERENCES [dbo].[CLIENTS] ([IDCLIENT])
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon] CHECK CONSTRAINT [FK_Manquantpompisteparstatioon_CLIENTS]
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon]  WITH CHECK ADD  CONSTRAINT [FK_Manquantpompisteparstatioon_Employees] FOREIGN KEY([EM_ID])
REFERENCES [dbo].[Employees] ([EM_ID])
GO
 
ALTER TABLE [dbo].[Manquantpompisteparstatioon] CHECK CONSTRAINT [FK_Manquantpompisteparstatioon_Employees]
GO
 
 
----------------------------------------------------------------------------------------------------------------6
CREATE TABLE [dbo].[Depotsparstation](
	[IdDepots] [int] NOT NULL,
	[Dates] [date] NULL,
	[IDCLIENT] [bigint] NOT NULL,
	[Libelle] [nvarchar](250) NULL,
	[Montant] [decimal](18, 3) NULL,
PRIMARY KEY CLUSTERED 
(
	[IdDepots] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Depotsparstation]  WITH CHECK ADD  CONSTRAINT [FK_Depotsparstation_CLIENTS] FOREIGN KEY([IDCLIENT])
REFERENCES [dbo].[CLIENTS] ([IDCLIENT])
GO
 
ALTER TABLE [dbo].[Depotsparstation] CHECK CONSTRAINT [FK_Depotsparstation_CLIENTS]
GO

en suivant ton exemple, j'ai écrit la requête suivante:

Code:

1
2
3
4
5
6
7
8
9
10
WITH TMANQUANT ([IdM],MDT,[MFK],[MMT])
AS (SELECT [IdManquantpompist],[Dates],[IDCLIENT],[Montant] FROM [dbo].[Manquantpompisteparstatioon])
,TDEPOT ([IdD],DDT,[DFK],[DMT])
AS (SELECT [IdDepots],[Dates],[IDCLIENT],[Montant] FROM [dbo].[Depotsparstation])
,TCUMUL(XFK, XDT,XMT) AS
(SELECT [DFK],DDT,SUM([DMT]) FROM TDEPOT GROUP BY DDT,[DFK])
SELECT [MFK], XDT,SUM([MMT]) AS MANQUANT,XMT AS DEPOT
 FROM TMANQUANT M
FULL OUTER JOIN TCUMUL X ON XFK=MFK AND MDT=XDT
 GROUP BY MFK, XDT,XMT

mais je constate que lorsque dans l'une des tables le montant est nulle, le résultat nés plus juste
Pièce jointe 437181

28/12/2018, 12h03
escartefigue

Vu que les montants sont "nullables" il faut utiliser sum(COALESCE(montant, 0)). ;)

Citation:

Envoyé par escartefigue

Vu que les montants sont "nullables" il faut utiliser sum(COALESCE(montant, 0)). ;)

Non, pas nécessaire !
Code:

1 2 3 4 5 6 7 ; WITH SR AS ( SELECT 1 AS val UNION ALL SELECT 2 UNION ALL SELECT NULL ) SELECT SUM(val) FROM SR ----------- 3 Warning: Null value is eliminated by an aggregate or other SET operation. (1 row affected)

28/12/2018, 15h16
escartefigue

Si parceque j'ai répondu trop vite, il n'y a pas de somme, le montant est utilisé tel quel :D
il faut donc utiliser COALESCE(montant, 0) ;)

3 pièce(s) jointe(s)

Bonjour escartefigue voila j'ai fait plusieurs enregistrements dans les deux tables au date différentes
- table Manquant
Pièce jointe 437467
- table Depot
Pièce jointe 437469
et la requête
Code:

1 2 3 4 5 6 7 8 9 10 WITH TMANQUANT ([IdM],[MFK],[MMT]) AS (SELECT [IdManquantpompist],[IDCLIENT],COALESCE([Montant],0) FROM [dbo].[Manquantpompisteparstatioon]) ,TDEPOT ([IdD],DDT,[DFK],[DMT]) AS (SELECT [IdDepots],[Dates],[IDCLIENT],COALESCE([Montant],0) FROM [dbo].[Depotsparstation]) ,TCUMUL(XFK, XDT,XMT) AS (SELECT [DFK],DDT,SUM(COALESCE([DMT],0)) FROM TDEPOT GROUP BY DDT,[DFK]) SELECT [MFK], XDT,SUM( COALESCE([MMT],0)) AS MANQUANT,COALESCE([XMT],0) AS DEPOT FROM TMANQUANT M FULL OUTER JOIN TCUMUL X ON XFK=MFK GROUP BY MFK, XDT,XMT
-Résultat
Pièce jointe 437473
je constate que lorsque les dates ne sont pas les même dans les deux tables elles ne s'affiche pas et la colonne MANQUANT ne groupe pas par date elle fais plutôt la somme de tous les montants de la table manquant