Discussion :

[asteblif]

Bonjour ,
Requête SQL avec MS SQL server , pour simplifier
Table Analyses_et_Bilans
TESTID PK
TESTTYPE si valeur 1 analyse élémentaire , si valeur 4 c’est un Bilan (comporte N analyses)
COTATION

TESTID TESTTYPE COTATION Liste des analyses et bilans du Patient
---- ---------- --------------
10 1 80
20 4 130
30 1 60
40 1 10
50 1 20


Table BILANS
TESTID PK
COMBTESTID PK,FK
Remarque :
La COTATION d’un bilan est inférieure à la somme des cotations de chaque analyse élémentaire.
(ci-dessous le bilan TESTID =20 composé des analyses élémentaires 10 et 30 vaut 130 et non pas (80+60)
TESTID COMBTESTID OrdreAna
---- ------------ --------------
20 10 1
20 30 2
70 50 6

Je veux sélectionner les bilans et les analyses élémentaires qui ne sont pas dans les bilans de la liste ci-dessus
En utilisant LEFT OUTJOIN J’y arrive presque :

Code :

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

1
2
3
4
5
Select TESTID , COTATION from Table Analyses_et_Bilans T1 
LEFT OUTER JOIN BILANS as T2
ON T1.TESTID=T2.COMBTESTID and TESTTYPE=’1’
where 
T2.COMBTESTID IS NULL

J’ai bien les bilans et analyses élémentaires qui ne sont pas incluses dans les bilans.
J’obtiens dans mon exemple les TESTID : 20 et 40 , il me manque la valeur 50
Donc il me manque des analyses élémentaires qui appartiennent à des bilans autres que ceux de ma liste pour mon patient.
Pourriez-vous m’aider svp ?

[Waldar]

Vous vous êtes trompé dans votre jointure, et le TESTTYPE faisant partie de la table principale il faut mettre le filtre dans le WHERE :

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
With Analyse (TESTID, TESTTYPE, COTATION) AS
(
select 10, 1,  80 union all
select 20, 4, 130 union all 
select 30, 1,  60 union all
select 40, 1,  10 union all
select 50, 1,  20
)
  ,  Bilan (TESTID, COMBTESTID, OrdreAna) AS
(
select 20, 10, 1 union all
select 20, 30, 2 union all
select 70, 50, 6
)
SELECT
    T1.TESTID,
    T1.COTATION
FROM
    Analyse AS T1 
    LEFT OUTER JOIN Bilan AS T2
      ON T2.TESTID = T1.TESTID 
WHERE 
    T2.COMBTESTID IS NULL
AND T1.TESTTYPE = 1;
 
TESTID      COTATION
----------- -----------
10          80
30          60
40          10
50          20

[mikedavem]

Bonjour,

Une autre manière d'écrire votre requête en sus de celle de Waldar. (Excuse Waldar je t'ai piqué ta façon de présenter sur ce coup , pas de copyright j'espère ...)

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
WITH Analyse (TESTID, TESTTYPE, COTATION) AS
(
SELECT 10, 1,  80 union ALL
SELECT 20, 4, 130 union ALL 
SELECT 30, 1,  60 union ALL
SELECT 40, 1,  10 union ALL
SELECT 50, 1,  20
)
  ,  Bilan (TESTID, COMBTESTID, OrdreAna) AS
(
SELECT 20, 10, 1 union ALL
SELECT 20, 30, 2 union ALL
SELECT 70, 50, 6
)
SELECT
    T1.TESTID,
    T1.COTATION
FROM
    Analyse AS T1 
WHERE NOT EXISTS (SELECT * FROM Bilan AS T2
				  WHERE T2.TESTID = T1.TESTID)
 AND T1.TESTTYPE = 1;
 
TESTID      COTATION
----------- -----------
10          80
30          60
40          10
50          20

++

[Waldar]

Une autre manière d'écrire votre requête en sus de celle de Waldar. (Excuse Waldar je t'ai piqué ta façon de présenter sur ce coup , pas de copyright j'espère ...)

Non au contraire, c'est plus facile si c'est toujours le même jeu d'essai !

[asteblif]

Vous vous êtes trompé dans votre jointure, et le TESTTYPE faisant partie de la table principale il faut mettre le filtre dans le WHERE :

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
With Analyse (TESTID, TESTTYPE, COTATION) AS
(
select 10, 1,  80 union all
select 20, 4, 130 union all 
select 30, 1,  60 union all
select 40, 1,  10 union all
select 50, 1,  20
)
  ,  Bilan (TESTID, COMBTESTID, OrdreAna) AS
(
select 20, 10, 1 union all
select 20, 30, 2 union all
select 70, 50, 6
)
SELECT
    T1.TESTID,
    T1.COTATION
FROM
    Analyse AS T1 
    LEFT OUTER JOIN Bilan AS T2
      ON T2.TESTID = T1.TESTID 
WHERE 
    T2.COMBTESTID IS NULL
AND T1.TESTTYPE = 1;
 
TESTID      COTATION
----------- -----------
10          80
30          60
40          10
50          20

Merci beaucoup pour votre réponse .
Mais je n'ai pas suffisamment bien expliqué.
Je veux sélectionner de T1 les analyses élémentaires (TESTTYPE=1) qui ne font pas partie d'un bilan (TESTTYPE=4) qui existe dans T1 , c'est à dire :

• cas1 : donc pas de COMBTESTID correspondant à TESTID de T1
• cas2 : ou si le (ou les) COMBTESTID=T1.TESTID et si le (ou les) T2.TESTID ne fait pas partie des bilans sélectionnés dans T1

et aussi les bilans (TESTTYPE=4)
Dans ce cas je devrais obtenir les TESTID :
20 car TESTYPE=4
40 rien dans BILANS colonne COMBTESTID
50 existe dans BILANS colonne COMBTESTID mais avec TESTID=70 qui n'est pas dans T1

J'espère que ces précisions vous permettront de mieux comprendre le problème.

[Waldar]

Essayez ainsi :

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
WITH Analyse (TESTID, TESTTYPE, COTATION) AS
(
SELECT 10, 1,  80 union ALL
SELECT 20, 4, 130 union ALL 
SELECT 30, 1,  60 union ALL
SELECT 40, 1,  10 union ALL
SELECT 50, 1,  20
)
  ,  Bilan (TESTID, COMBTESTID, OrdreAna) AS
(
SELECT 20, 10, 1 union ALL
SELECT 20, 30, 2 union ALL
SELECT 70, 50, 6
)
SELECT
    TESTID,
    COTATION
FROM
    Analyse
WHERE
    TESTTYPE = 4
 OR (TESTTYPE = 1
 AND TESTID NOT IN (SELECT T2.COMBTESTID
                    FROM Bilan AS T2
                         INNER JOIN Analyse AS T3
                           ON T3.TESTID = T2.TESTID
                    WHERE T3.TESTTYPE = 4));
 
TESTID      COTATION
----------- -----------
20          130
40          10
50          20

[asteblif]

Essayez ainsi :

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
WITH Analyse (TESTID, TESTTYPE, COTATION) AS
(
SELECT 10, 1,  80 union ALL
SELECT 20, 4, 130 union ALL 
SELECT 30, 1,  60 union ALL
SELECT 40, 1,  10 union ALL
SELECT 50, 1,  20
)
  ,  Bilan (TESTID, COMBTESTID, OrdreAna) AS
(
SELECT 20, 10, 1 union ALL
SELECT 20, 30, 2 union ALL
SELECT 70, 50, 6
)
SELECT
    TESTID,
    COTATION
FROM
    Analyse
WHERE
    TESTTYPE = 4
 OR (TESTTYPE = 1
 AND TESTID NOT IN (SELECT T2.COMBTESTID
                    FROM Bilan AS T2
                         INNER JOIN Analyse AS T3
                           ON T3.TESTID = T2.TESTID
                    WHERE T3.TESTTYPE = 4));
 
TESTID      COTATION
----------- -----------
20          130
40          10
50          20

Encore merci pour cette excellente réponse .
J'ai essayé de l'appliquer scrupuleusement à mes données , j'avais simplifié , le TESTYPE vient d'une table DICT_TESTS , mais ça devrait marcher.

TESTS correspond à Analyse
DICT_TEST_COMP à Bilan

Je ne m'en sors pas , est-ce à cause des AND ?
Et bien que je mette ... (DT.TESTTYPE = 1) AND (T1.REQUESTID = '531960') le formatage dee SQLServer me rend ceci ci-dessous.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT     T1.TESTID, T1.REQUESTID, DT.LISTESTCODE, DT.ENDVALIDDATE, DT.SHORTTEXT, DT.KEYLETTERCODE, DT.NBKEYLETTER AS Cotation, 
                      DT.TESTTYPE
FROM         TESTS AS T1 INNER JOIN
                      DICT_TESTS AS DT ON T1.TESTID = DT.TESTID AND DT.KEYLETTERCODE IS NOT NULL
WHERE     (DT.TESTTYPE = 4) AND (T1.REQUESTID = '531960') OR
                      (DT.TESTTYPE = 1) AND (T1.TESTID NOT IN
                          (SELECT     T2.COMBTESTID
                            FROM          DICT_TEST_COMP AS T2 INNER JOIN
                                                   TESTS AS T3 ON T3.TESTID = T2.TESTID
                            WHERE      (DT.TESTID = T3.TESTID) AND (DT.TESTTYPE = 4) AND (DT.KEYLETTERCODE IS NOT NULL))) AND (T1.REQUESTID = '531960')
ORDER BY T1.TESTID

Pourriez-vous svp encore une fois m'aider ?

et si ce n'est pas trop demander :
est-il aussi possible de générer en plus la somme des cotations (qui est de type vchar) sur une ligne ou colonne supplémentaire ?

[Waldar]

A priori c'est un problème au niveau des parenthèses, et un petit mélange de syntaxe not in / not exists.

J'ai regardé la requête en vitesse donc ce n'est probablement pas 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
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
    T1.TESTID,
    T1.REQUESTID,
    DT.LISTESTCODE,
    DT.ENDVALIDDATE,
    DT.SHORTTEXT,
    DT.KEYLETTERCODE,
    DT.NBKEYLETTER AS Cotation, 
    DT.TESTTYPE
FROM
    TESTS AS T1
    INNER JOIN DICT_TESTS AS DT
      ON T1.TESTID = DT.TESTID
WHERE 
    DT.KEYLETTERCODE IS NOT NULL
AND T1.REQUESTID = '531960'
AND (
     (DT.TESTTYPE = 4)
  OR (DT.TESTTYPE = 1
    AND T1.TESTID NOT IN (
                          SELECT T2.COMBTESTID
                          FROM DICT_TEST_COMP AS T2
                               INNER JOIN TESTS AS T3
                                 ON T3.TESTID = T2.TESTID
                          WHERE T2.TESTTYPE = 4
                          AND T2.KEYLETTERCODE IS NOT NULL
                          AND T3.REQUESTID = '531960'
                         )
     )
    )
ORDER BY
    T1.TESTID ASC

[asteblif]

A priori c'est un problème au niveau des parenthèses, et un petit mélange de syntaxe not in / not exists.

J'ai regardé la requête en vitesse donc ce n'est probablement pas 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
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
    T1.TESTID,
    T1.REQUESTID,
    DT.LISTESTCODE,
    DT.ENDVALIDDATE,
    DT.SHORTTEXT,
    DT.KEYLETTERCODE,
    DT.NBKEYLETTER AS Cotation, 
    DT.TESTTYPE
FROM
    TESTS AS T1
    INNER JOIN DICT_TESTS AS DT
      ON T1.TESTID = DT.TESTID
WHERE 
    DT.KEYLETTERCODE IS NOT NULL
AND T1.REQUESTID = '531960'
AND (
     (DT.TESTTYPE = 4)
  OR (DT.TESTTYPE = 1
    AND T1.TESTID NOT IN (
                          SELECT T2.COMBTESTID
                          FROM DICT_TEST_COMP AS T2
                               INNER JOIN TESTS AS T3
                                 ON T3.TESTID = T2.TESTID
                          WHERE T2.TESTTYPE = 4
                          AND T2.KEYLETTERCODE IS NOT NULL
                          AND T3.REQUESTID = '531960'
                         )
     )
    )
ORDER BY
    T1.TESTID ASC

Bonsoir ,
c'est SUPER !!!
Ca marche il manquait uniquement INNER JOIN pour déterminer le TESTTYPE de T2 car il est dans DICT_TESTS.
Je vous remercie encore , Waldar , d'avoir pris le temps d'étudier mon problème , et merci aussi
à Mikedavem qui s'est intéressé à la question .
Vous êtes simplement formidables.

Dois-je clore cette discussion mais j'aurais aimé avoir tout au moins une indication (possible ou pas) à la question complémentaire
posée hier pour laquelle n'ai rien trouvé pour m'aider dans le forum :
"est-il aussi possible de générer en plus la somme des cotations (qui est de type vchar) sur une ligne ou colonne supplémentaire ?"
(Cette requête sera utilisée pour tous les REQUESTID)

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
SELECT
    T1.TESTID,
    T1.REQUESTID,
    DT.LISTESTCODE,
    DT.ENDVALIDDATE,
    DT.SHORTTEXT,
    DT.KEYLETTERCODE,
    DT.NBKEYLETTER AS Cotation, 
    DT.TESTTYPE
FROM
    TESTS AS T1
    INNER JOIN DICT_TESTS AS DT
      ON T1.TESTID = DT.TESTID
WHERE 
    DT.KEYLETTERCODE IS NOT NULL
AND T1.REQUESTID = '531960'
AND (
     (DT.TESTTYPE = 4)
  OR (DT.TESTTYPE = 1
    AND T1.TESTID NOT IN (
                          SELECT T2.COMBTESTID
                          FROM DICT_TEST_COMP AS T2
                               INNER JOIN TESTS AS T3
                                 ON T3.TESTID = T2.TESTID
                               INNER JOIN DICT_TESTS AS DT2 
                                 ON DT2.TESTID = T2.TESTID
                          WHERE DT2.TESTTYPE = 4
                          AND DT2.KEYLETTERCODE IS NOT NULL
                          AND T3.REQUESTID = '531960'
                         )
     )
    )
ORDER BY
    T1.TESTID ASC

[Waldar]

Il faudra convertir vos cotations en nombre et ensuite utiliser l'opérateur SUM dessus (et donc rajouter un GROUP BY à votre requête).