Problème de jointure "complexe"

Bonjour,

J'ai des tiers (point de livraison, enseigne) dans une table de tiers "tie".

Ces tiers sont liés entre eux à l'aide d'une table des groupes de tiers "tig", où chaque tiers est associé à sa hiérarchie complète. Par exemple, pour le code d'un point de livraison, j'ai son groupe et son enseigne ; pour un groupe, j'ai son enseigne.

Ensuite, j'ai des contrats "enseigne", qui sont des contrats "globaux", négociés pour toute une enseigne ou un groupe. Parfois un point de livraison directement.
Ces contrats enseigne donnent lieu à des contrats PDL pour tous les PDL qui sont dans le groupe du tiers sur lequel ils portent.
Par exemple, contrat enseigne sur "enseigne 1" donnera lieu à des contrats PDL sur les points de livraison 1, 2 et 3 ; un contrat sur le "groupe 1" donnera lieu à des contrats PDL sur les pdl 1 et 2, et un contrat enseigne sur le PDL 1 donnera lieu à un contrat PDL sur le PDL 1.
Les contrats PDL ont le même numéro de contrat que le contrat ENS auquel ils sont rattachés.

Il s'agit d'un ERP, je dois faire avec ce modèle des données. Pas de modification possible.

Voici la structure (simplifiée) des tables :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 create table tie ( tie_id int not null identity(1, 1) primary key, tie_nom varchar(30) not null ); create table tig ( tig_id int not null identity(1, 1) primary key, tie1_id int not null references tie(tie_id), tie2_id int not null references tie(tie_id) ); create table cnt ( cnt_id int not null identity(1, 1) primary key, tie_id int not null references tie(tie_id), cnt_type char(3) not null, cnt_num int not null );
Dans un traitement, j'ai besoin de retrouver, à partir d'un certain nombre de contrats PDL, les contrats enseigne associés.

Jeu de test :
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 insert into tie (tie_nom) values ('Enseigne 1'); insert into tie (tie_nom) values ('Enseigne 2'); insert into tie (tie_nom) values ('Groupe 1'); insert into tie (tie_nom) values ('Groupe 2'); insert into tie (tie_nom) values ('Groupe 3'); insert into tie (tie_nom) values ('Point de livraison 1'); insert into tie (tie_nom) values ('Point de livraison 2'); insert into tie (tie_nom) values ('Point de livraison 3'); insert into tie (tie_nom) values ('Point de livraison 4'); insert into tig (tie1_id, tie2_id) values (3, 1); insert into tig (tie1_id, tie2_id) values (4, 1); insert into tig (tie1_id, tie2_id) values (5, 2); insert into tig (tie1_id, tie2_id) values (6, 1); insert into tig (tie1_id, tie2_id) values (7, 1); insert into tig (tie1_id, tie2_id) values (8, 1); insert into tig (tie1_id, tie2_id) values (9, 2); insert into tig (tie1_id, tie2_id) values (6, 3); insert into tig (tie1_id, tie2_id) values (7, 3); insert into tig (tie1_id, tie2_id) values (8, 4); insert into tig (tie1_id, tie2_id) values (9, 5); insert into cnt (tie_id, cnt_type, cnt_num) values (1, 'ENS', 1); insert into cnt (tie_id, cnt_type, cnt_num) values (6, 'PDL', 1); insert into cnt (tie_id, cnt_type, cnt_num) values (7, 'PDL', 1); insert into cnt (tie_id, cnt_type, cnt_num) values (8, 'PDL', 1); insert into cnt (tie_id, cnt_type, cnt_num) values (2, 'ENS', 2); insert into cnt (tie_id, cnt_type, cnt_num) values (9, 'PDL', 2);
Je veux par exemple retrouver les contrats PDL dont le numéro de contrat est "1", ainsi que leur contrat enseigne rattaché :
Code:

1 2 3 4 5 6 select * from cnt pdl inner join cnt ens on ens.cnt_num = pdl.cnt_num and ens.cnt_type = 'ENS' where pdl.cnt_type = 'PDL' and pdl.cnt_num = 1;
Jusque là, tout va bien.

Au détail près que parfois, pour un numéro de contrat donné, j'ai plusieurs contrats enseigne.

Jeu de données :
Code:

1 2 3 insert into cnt (tie_id, cnt_type, cnt_num) values (2, 'ENS', 1); insert into cnt (tie_id, cnt_type, cnt_num) values (9, 'PDL', 1);
Je décide donc de passer par ma table tig pour retrouver le contrat enseigne qui correspond aux contrats PDL où le tiers est de la même famille :
Code:

1 2 3 4 5 6 7 select * from cnt pdl inner join tig on tig.tie1_id = pdl.tie_id inner join cnt ens on ens.cnt_num = pdl.cnt_num and ens.cnt_type = 'ENS' and ens.tie_id = tig.tie2_id where pdl.cnt_type = 'PDL' and pdl.cnt_num = 1;
Jusque là, tout va bien !

Seulement voilà, j'ai aussi des contrats enseigne créés directement sur un PDL, qui donnent lieu à un contrat PDL identique au détail près qu'il n'est pas du même type.
A ce moment, je n'ai aucune ligne qui correspond dans TIG.

Jeu de données :
Code:

1 2 3 4 5 6 7 insert into cnt (tie_id, cnt_type, cnt_num) values (1, 'ENS', 3); insert into cnt (tie_id, cnt_type, cnt_num) values (6, 'PDL', 3); insert into cnt (tie_id, cnt_type, cnt_num) values (7, 'PDL', 3); insert into cnt (tie_id, cnt_type, cnt_num) values (8, 'PDL', 3); insert into cnt (tie_id, cnt_type, cnt_num) values (9, 'ENS', 3); insert into cnt (tie_id, cnt_type, cnt_num) values (9, 'PDL', 3);
Je rajoute donc une clause où je passe par TIG si j'ai bien un lien entre tie_id des contrats ENS et PDL, ou si c'est le même tie_id entre ENS et PDL :
Code:

1 2 3 4 5 6 7 8 select * from cnt pdl inner join tig on tig.tie1_id = pdl.tie_id inner join cnt ens on ens.cnt_num = pdl.cnt_num and ens.cnt_type = 'ENS' and (ens.tie_id = tig.tie2_id or ens.tie_id = pdl.tie_id) where pdl.cnt_type = 'PDL' and pdl.cnt_num = 3;
Seulement voilà, pour mon contrat PDL 14, qui porte sur le tiers 9, j'ai un doublon, puisque dans TIG j'ai deux lignes, que je n'utilise pas.

Comment faire pour éviter de devoir rajouter des sous-requêtes dans tous les sens pour faire la jointure par TIG si et seulement si tie_id est différent de pdl_id.