Problème avec une requête

Version imprimable

Bonjour,
j'ai un petit soucis avec une requête, je cherche à avoir la liste des personnes, qui ont un numéro de contrat présent dans plusieurs dossiers .
Ma requête m'affiche bien en grande majorité la liste des personnes, avec même numéro de contrat, mais un numéro de dossier différents ( plusieurs lignes donc ), mais j'ai aussi les personnes avec un dossier unique, donc une seul ligne, et je veux les exclure, pour qu'il me reste les personnes, qui ont pour un même numéro de contrat plusieurs dossiers.

Voici ma requete
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select distinct t1.identifiant, t2.nom, t2.prenom, t3.nomContrat, t4.numerodossier from tabledossier t4 inner join table1 t1 on t1.id=t4.idrefTable1 inner join tInfoPersonne t2 on t2.idrefTable1 = t1.idrefTable1 inner join tcontrat t3 t3.idrefTable1 = t1.idrefTable1 table1(id,identifiant,...) tInfoPersonne(id, npm,prenom,idrefTable1) tContrat(id,nomContrat,....,idrefTable1) tabledossier(id,numerodossier,...,idrefTable1)
J'avais pensé à faire un having, mais je sais pas trop comment m'y prendre avec ça.

merci à vous

Si tu veux utiliser HAVING, il faut faire un regroupement au niveau du contrat sans détailler par dossier :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
select distinct 
        t1.identifiant
    ,   t2.nom
    ,   t2.prenom
    ,   t3.nomContrat
--     ,   t4.numerodossier      
from    tabledossier    t4
    inner join 
        table1          t1 
        on  t1.id = t4.idrefTable1
    inner join 
        tInfoPersonne   t2 
        on  t2.idrefTable1 = t1.idrefTable1
    inner join 
        tcontrat        t3 
        on  t3.idrefTable1 = t1.idrefTable1
group by t1.identifiant
    ,   t2.nom
    ,   t2.prenom
    ,   t3.nomContrat
having  count(t4.numerodossier) > 1

Si tu veux connaître les dossiers répondant à ce critère, il faut utiliser une autre méthode avec EXISTS :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select distinct 
        t1.identifiant
    ,   t2.nom
    ,   t2.prenom
    ,   t3.nomContrat
    ,   t4.numerodossier      
from    tabledossier    t4
    inner join 
        table1          t1
        on  t1.id = t4.idrefTable1
    inner join
        tInfoPersonne   t2
        on  t2.idrefTable1 = t1.idrefTable1
    inner join
        tcontrat        t3
        on  t3.idrefTable1 = t1.idrefTable1
where   exists
        (   select  null
            from    tabledossier    t5
            where   t1.id = t5.idrefTable1
            having  count(t5.numerodossier) > 1
        )

16/05/2018, 10h51
android59

merci, la 1ere requete est bien elle correspond en partie.
après quelques tests, pour une personne j'ai bien le nom, prenom numéro contrat, nombre de dossier(je l'ai rajouté)dans lequel le numéro de contrat se trouve:

identifiant nom prenom nb_dossiers numero dossier num contrat
123456 odile croc 4 8072248 300020665800000
789654 cane gery 3 17067055 30002654564654000

seulement je trouve un peu étrange que j'ai aussi peu de résultats, dans ma requête initiale j'ai 8300 lignes alors que celle ci ne me retourne que 18 lignes
voici un exemple ce que j'obtiens avec ma requete initiale:

identifiant nom prenom numero dossier num contrat
123456 odile croc 8072248 300020665800000
123456 odile croc 1234567 300020665800000
123456 odile croc 98745sd 300020665800000
123456 odile croc 52369870 300020665800000
123456 odile croc 258147963 300020665800000
789654 cane gery 222222222 30002654564654000
789654 cane gery 111111111 30002654564654000
789654 cane gery 3333333333 30002654564654000
789654 cane gery 1555555555 30002654564654000
789654 cane gery 184545454545 30002654564654000
789654 cane gery 9898989898 30002654564654000

est il possible donc que la liste des personnes, ayant pour chaque numéro contrat un numéro de dossier différent, en ne comptant pas les personnes ayant qu'un seul dossier ? un grand merci!

j'ai rien dis, je pense avoir saisie.

Lorsque je fais la seconde requete

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
 
 
select distinct 
        t1.identifiant
    ,   t2.nom
    ,   t2.prenom
    ,   t3.nomContrat
    ,   t4.numerodossier      
from    tabledossier    t4
    inner join 
        table1          t1
        on  t1.id = t4.idrefTable1
    inner join
        tInfoPersonne   t2
        on  t2.idrefTable1 = t1.idrefTable1
    inner join
        tcontrat        t3
        on  t3.idrefTable1 = t1.idrefTable1
where   exists
        (   select  null
            from    tabledossier    t5
            where   t1.id = t5.idrefTable1
            having  count(t5.numerodossier) > 1
        )

j'ai le message d'erreur suivant :s :

ORA-00937: la fonction de groupe ne porte pas sur un groupe simple
00937. 00000 - "not a single-group group function, j'ai un peu de mal à saisir :s

Code:

1
2
3
4
5
select  1
from    tabledossier    t5
where   t1.id = t5.idrefTable1
GROUP BY t5.numerodossier
having  count(t5.numerodossier) > 1

17/05/2018, 11h03
android59

Merci

petite interrogation : pourquoi lors de mes tests ( et ça m'agace bien comme il faut), si je veux avoir les personnes qui ont un numéro de contrat dans plusieurs dossiers, malgré le having count >1, lors de mes tests, j'ai toujours des lignes récalcitrantes qui n'ont rien à faire là.

Je m'explique:
test 1 : je prend son numero dossier, j'ai bien plusieurs n° de contrat : ok
test 2 : 1 numero et là j'ai 1 seul contrat : KO
test 3 : 1 numero de dossier et là j'ai 3 numero contrat donc là ok

pourtant j'ai bien ma requête bête et idiote qui dit que je veux ma liste de personne avec mon having count n°dossier>1, vous auriez une idée sur la question ou c'est sqldevelopper qui a décidé de m'agacer pour rester politiquement correct lol ?
p.s : meme si je fais mon having count numdossier > 2 , il me met des lignes unique :s

merci à vous :)
17/05/2018, 11h37
al1_24

As-tu essayé avec having count(distinct t5.numerodossier) ?
18/05/2018, 08h20
android59

si je met un distinct, il ne me met aucun résultat :s sinon de temps en temps j'ai un meme n° de contrat pour plusieurs dossiers mais apres j'ai tjrs des enregistrements unique et on me demande d'afficher que les lignes en double ou plus ( qui ont pour un même n° de contrat plusieurs n° dossiers)

n'y a t-il pas quelque chose a rajouté dans la premiere sous requete car si je mets exists, j'ai vu que c'est la requete principal qui s'execute s'il existe un resultat dans la sous requete

petite question, je ne cherche toutes la liste des personnes pour lesquels il y a un meme numéro de contrat dans plusieurs dossiers, le having count ne doit il pas porter sur le n°de contrat ?

:s
18/05/2018, 08h59
android59

petite question, dans le having count, ne doit il pas porter sur le numéro de contrat puisque je cherche à avoir la liste des personnes dont le n° de contrat est présent sur plusieurs dossiers distinct, j'avais donc pensé à un truc du genre :

having count(n°contrat)>1.

Je viens de penser à une chose, en gros je cherche les numéro de contrat en doublon tout simplement, il me reste plus qu'a ajouter le numéro de dossier et c'est bon, mais là il me faut juste les doublons ?

Bonjour,

Ne connaissant pas trop les contraintes d'unicité dans et entre les tables, j'ai laissé le DISTINCT dans le sous-select.

Code:

1
2
3
4
5
6
7
8
9
10
SELECT identifiant, nom, prenom, nomContrat, listagg(numerodossier,', ') WITHIN GROUP (ORDER BY numerodossier) lstnumdoss, count(*) nbdoss
FROM (
SELECT DISTINCT t1.identifiant, t2.nom, t2.prenom, t3.nomContrat, t4.numerodossier
FROM TABLE1 t1, TINFOPERSONNE t2, TCONTRAT t3, TABLEDOSSIER t4
WHERE t1.ID=t4.idrefTable1
AND t2.idrefTable1 = t1.idrefTable1
AND t3.idrefTable1 = t1.idrefTable1
)
GROUP BY identifiant, nom, prenom, nomContrat
HAVING MIN(numerodossier) <> MAX(numerodossier)

29/05/2018, 14h30
android59

merci pour ton retour, mais ça ne marche pas, de la façon dont tu as fait la requête, j'ai rajouté ou supprimé des champs, ça me dit toujours " invalid identifier"
Meme en la prenant tel quel mais en la modifiant pour ma requête, j'ai une erreur.

Aurais tu une autre façon d'écrire la requête, que je comprenne mieux, j'ai essayé par tatillon et j'ai réussi à obtenir le nombre de n° de contrat par personne, mais en rajoutant les dossiers, ça me sert plus a rien car il me faut une liste et la requête m'affiche le nombre ex :

identifiant nb numdossier n°contrat
12345 4 654789 ccccc
12345 2 654987 xxxxxx
12345 2 000000000 xxxxxx
654987 3 000001111
654987 3 11111111

et moi je voudrais
12345 654789 cccccc
12345 654789 ddddd
12345 654789 bbbvvv
12345 654789 vvvvvv
12345 654987 xxxxxx
12345 654987 bbbbbb
etc...

est possible d'avoir une tel liste en requête ?
Merci

Bon, on va arrêter et reprendre à zéro car il nous manque la base.

Donne nous un exemple de données (pour chaque table), et le résultat attendu

exemple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
table1
SELECT 1 ID, 200 identifiant FROM dual
UNION ALL SELECT 2,343 FROM dual
 
tContrat
SELECT 1 ID, 'aa' nomContrat, 1 idreftable
UNION ALL  SELECT 2 ID, 'aa' nomContrat, 2 idreftable
..
 
 
Attendu 
identi  numcontrat nom
12345 654789 cccccc
12345 654789 ddddd
12345 654789 bbbvvv
12345 654789 vvvvvv
12345 654987 xxxxxx
12345 654987 bbbbbb

http://sqlfiddle.com/#!4/c5815b/16

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
53
 
create table t_person (
  id number(19,0) primary key,
  name varchar2(256 char)
);
 
create table t_contract(
  id number(19,0) primary key,
  person_id number(19,0) constraint fk_contract_person references t_person(id),
  name varchar2(256 char)
);
 
create table t_dossier (
  id number(19,0) primary key,
  contract_id number(19,0) constraint fk_dossier_contract references  t_contract(id),
  name varchar2(256 char)
);
 
 
insert into t_person(id, name) values(1,'person with 1 contract 0 dossier') ;
insert into t_person(id, name) values(2,'person with 1 contract 1 dossier') ;
insert into t_person(id, name) values(3,'person with 1 contract 2 dossier') ;
insert into t_person(id, name) values(4,'person with 2 contract each 1 dossier') ;
insert into t_person(id, name) values(5,'person with 3 contract (0,1,2) dossier') ;
 
insert into t_contract(id, person_id, name) values(1,1,'contract of p 1') ;
insert into t_contract(id, person_id, name) values(2,2,'contract of p 2') ;
insert into t_contract(id, person_id, name) values(3,3,'contract of p 3') ;
insert into t_contract(id, person_id, name) values(4,4,'contract 1 of p 4') ;
insert into t_contract(id, person_id, name) values(5,4,'contract 2 of p 4') ;
insert into t_contract(id, person_id, name) values(6,5,'contract 1 of p 5') ;
insert into t_contract(id, person_id, name) values(7,5,'contract 2 of p 5') ;
insert into t_contract(id, person_id, name) values(8,5,'contract 3 of p 5') ;
 
 
insert into t_dossier(id, contract_id, name) values(1,2,'dossier of contract 1 of p 2') ;
insert into t_dossier(id, contract_id, name) values(2,3,'dossier 1 of contract 1 of p 3') ;
insert into t_dossier(id, contract_id, name) values(3,3,'dossier 2 of contract 1 of p 3') ;
insert into t_dossier(id, contract_id, name) values(4,4,'dossier 1 of contract 1 of p 4') ;
insert into t_dossier(id, contract_id, name) values(5,5,'dossier 1 of contract 2 of p 4') ;
insert into t_dossier(id, contract_id, name) values(6,7,'dossier 1 of contract 2 of p 5') ;
insert into t_dossier(id, contract_id, name) values(7,8,'dossier 1 of contract 3 of p 5') ;
insert into t_dossier(id, contract_id, name) values(8,8,'dossier 2 of contract 3 of p 5') ;
 
with persons as (
select p.id from t_person p
  join t_contract c on c.person_id = p.id
  join t_dossier d on d.contract_id = c.id
  group by p.id, c.id
  having count(distinct c.id || '-' || d.id) > 1
)
select * from t_person where id in (select id from persons)
;

retourne person ID 3 et 5

Citation:
Envoyé par McM
Bon, on va arrêter et reprendre à zéro car il nous manque la base.

Donne nous un exemple de données (pour chaque table), et le résultat attendu

exemple
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 table1 SELECT 1 ID, 200 identifiant FROM dual UNION ALL SELECT 2,343 FROM dual tContrat SELECT 1 ID, 'aa' nomContrat, 1 idreftable UNION ALL SELECT 2 ID, 'aa' nomContrat, 2 idreftable .. Attendu identi numcontrat nom 12345 654789 cccccc 12345 654789 ddddd 12345 654789 bbbvvv 12345 654789 vvvvvv 12345 654987 xxxxxx 12345 654987 bbbbbb
Bonjour,
est ce que ça peut vous aider cela ?

table contrat
id idreftable1 numcontrat
1 12345 654789
2 12azer 654987
3 13trez 987456
4 12345 654789zerezrzer
5 13trez 654987456546etert
6 12azer 987456789789879879
7 d2222 987456789789879879
8 d2222 111222336668888999/

table1
identifiant
12345
12azer
13trez
d1111
d2222
d3333

tabledossier
id numerodossier idgroupeTable1
1 dossier1 12345
2 dossier2 12azer
3 dossier3 13trez
4 dossier4 d1111
5 dossier5 d2222
6 dossier6 d3333

je viens de me rendre compte d'une bourde, en fait il y a une jointure entre table1(id) et tableContrat(idrefTable1) mais une autre jointure qui se fait entre tableDossier(idgroupeTable1 et non idreftable1) et table1(id):

et donc ce que je cherche c'est d'avoir toutes les lignes, d'avoir tous les n° de contrat par dossier, exemple :

dossier1 -- 12345 -- 654789
dossier1 -- 12345 -- 654789zerezrzer
dossier2 -- 12azer -- 654987
dossier2 -- 12azer -- 987456789789879879
dossier3 -- 12345 -- 987456
dossier3 -- 12345 -- 654987456546etert
etc....

est ce plus clair ? merci à vous :)

Avoir toutes les lignes.. c'est du sql basique

Code:

1
2
3
4
5
SELECT d.numerodossier, d.idgroupeTable1, c.numcontrat
FROM CONTRAT c, TABLE1 t, TABLEDOSSIER d
WHERE c.idreftable1 = t.identifiant
AND d.idgroupetable1 = t.identifiant
ORDER BY d.numerodossier, d.idgroupeTable1, c.numcontrat

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
WITH 
CONTRAT AS (SELECT 1 ID, '12345' idreftable1, '654789' numcontrat FROM DUAL
  UNION ALL SELECT 2 ID, '12azer' idreftable1, '654987' numcontrat FROM DUAL
  UNION ALL SELECT 3 ID, '13trez' idreftable1, '987456' numcontrat FROM DUAL
  UNION ALL SELECT 4 ID, '12345' idreftable1, '654789zerezrzer' numcontrat FROM DUAL
  UNION ALL SELECT 5 ID, '13trez' idreftable1, '654987456546etert' numcontrat FROM DUAL
  UNION ALL SELECT 6 ID, '12azer' idreftable1, '987456789789879879' numcontrat FROM DUAL
  UNION ALL SELECT 7 ID, 'd2222' idreftable1, '987456789789879879' numcontrat FROM DUAL
  UNION ALL SELECT 8 ID, 'd2222' idreftable1, '111222336668888999/' numcontrat FROM DUAL), 
TABLE1 AS (SELECT '12345' identifiant FROM dual
  UNION ALL SELECT '12azer' identifiant FROM dual
  UNION ALL SELECT '13trez' identifiant FROM dual
  UNION ALL SELECT 'd1111' identifiant FROM dual
  UNION ALL SELECT 'd2222' identifiant FROM dual
  UNION ALL SELECT 'd3333' identifiant FROM dual),
TABLEDOSSIER AS (SELECT 1 ID, 'dossier1' numerodossier, '12345' idgroupeTable1 FROM DUAL
UNION ALL SELECT 2 ID, 'dossier2' numerodossier, '12azer' idgroupeTable1 FROM DUAL
UNION ALL SELECT 3 ID, 'dossier3' numerodossier, '13trez' idgroupeTable1 FROM DUAL
UNION ALL SELECT 4 ID, 'dossier4' numerodossier, 'd1111' idgroupeTable1 FROM DUAL
UNION ALL SELECT 5 ID, 'dossier5' numerodossier, 'd2222' idgroupeTable1 FROM DUAL
UNION ALL SELECT 6 ID, 'dossier6' numerodossier, 'd3333' idgroupeTable1 FROM DUAL)
SELECT d.numerodossier, d.idgroupeTable1, c.numcontrat
FROM CONTRAT c, TABLE1 t, TABLEDOSSIER d
WHERE c.idreftable1 = t.identifiant
AND d.idgroupetable1 = t.identifiant
ORDER BY d.numerodossier, d.idgroupeTable1, c.numcontrat
 
NUMERODOSSIER	IDGROUPETABLE1	NUMCONTRAT
dossier1	12345	654789
dossier1	12345	654789zerezrzer
dossier2	12azer	654987
dossier2	12azer	987456789789879879
dossier3	13trez	654987456546etert
dossier3	13trez	987456
dossier5	d2222	111222336668888999/
dossier5	d2222	987456789789879879

Mais la demande était sur une liste de contrats sur plusieurs dossiers ou l'inverse, je sais plus.

01/06/2018, 08h29
android59

Citation:

Envoyé par McM

Avoir toutes les lignes.. c'est du sql basique

Mais la demande était sur une liste de contrats sur plusieurs dossiers ou l'inverse, je sais plus.

Oui certes, pardon, je reformule ce que j'ai dis, en fait c'est d'avoir toutes les lignes, d'avoir les dossiers qui ont plus de 2 n° de contrat donc par exemple:

dossier n°contrat
123456 contrat456789

dossier n°contrat
123456 contrat654978

dossier n°contrat
321654 az6598

dossier n°contrat
vvvvv 988ffffff

dossier n°contrat
vvvvv ggg78987

j'aimerais que la requete retourne tous les n° contrat pour un meme dossier, si le dossier contient + de 2 n° de contrat me suis tu ?

dans mon exemple :
Resultat :
dossier n°contrat
123456 contrat456789
123456 contrat654978
vvvvv ggg78987
vvvvv 988ffffff

les lignes dont il y a qu'un seul contrat pour le dossier ne doit pas apparaitre et c'est là où j'ai du mal car si j'essaye avec un having count il me sort
123456 contrat456789
vvvvv 988ffffff

or j'aimerais bien avoir tout

merci d'avance :)
01/06/2018, 11h10
McM

Donne un jeu de test comem je l'ai fait à mon post précédent, parce que tu repars sur des infos restreintes.