URL: https://linuxfr.org/forums/programmation-sql/posts/probl%C3%A8me-de-jointure-multiple
Title: Problème de jointure multiple
Authors: Ontologia
Date: 2006年04月26日T16:29:12+02:00
Tags: 
Score: 0
Bonjour, j'essaye toujours de migrer ma base oracle vers Postgre et je tombe sur un épineux problème de jointures multiples : 
 
J'ai pris le parti de recommencer à transformer le code 
 
je part donc de (format oracle) : 
 select a.*, 
 £spaces£ £/spaces£b.met_id,b.met_lib, 
 £spaces£ £/spaces£h.fct_id, 
 £spaces£ £/spaces£c.fct_lib, 
 £spaces£ £/spaces£d.ct_id,d.ct_lib,j.ct_valide, 
 £spaces£ £/spaces£e.typ_id,e.typ_lib, 
 £spaces£ £/spaces£f.notfct_val, 
 £spaces£ £/spaces£i.ser_lib 
 from 
 £spaces£ £/spaces£collaborateurs a, 
 £spaces£ £/spaces£metiers b, 
 £spaces£ £/spaces£fonction c, 
 £spaces£ £/spaces£competences_tech d, 
 £spaces£ £/spaces£type_ct e, 
 £spaces£ £/spaces£li_col_ct_fct f, 
 £spaces£ £/spaces£li_col_met g, 
 £spaces£ £/spaces£li_col_fct h, 
 £spaces£ £/spaces£service i, 
 £spaces£ £/spaces£LI_MET_TYP_CT j 
 where 
 £spaces£ £/spaces£(a.col_id=g.col_id(+) 
 £spaces£ £/spaces£and g.met_id=b.met_id(+)) 
 £spaces£ £/spaces£and (a.col_id=h.col_id(+) 
 £spaces£ £/spaces£and h.fct_id=c.fct_id(+)) 
 £spaces£ £/spaces£and (f.col_id=a.col_id and f.met_id=b.met_id and d.ct_id=f.ct_id) 
 £spaces£ £/spaces£and (j.typ_id=e.typ_id) 
 £spaces£ £/spaces£and (a.ser_id = i.ser_id(+)) 
 £spaces£ £/spaces£and (j.ct_id = d.ct_id) 
 £spaces£ £/spaces£and (j.met_id = g.met_id) 
 order by a.col_nom asc , c.fct_lib asc , e.typ_lib asc , j.ct_valide desc, d.ct_lib asc; 
 
 
à 
 select collaborateurs.*, 
 £spaces£ £/spaces£metiers.met_id,metiers.met_lib, 
 £spaces£ £/spaces£li_col_fc.fct_id, 
 £spaces£ £/spaces£fonction.fct_lib, 
 £spaces£ £/spaces£competences_tech.ct_id,competences_tech.ct_lib, 
 £spaces£ £/spaces£LI_MET_TYP_CT.ct_valide, 
 £spaces£ £/spaces£type_ct.typ_id,type_ct.typ_lib, 
 £spaces£ £/spaces£li_col_ct_fct.notfct_val, 
 £spaces£ £/spaces£service.ser_lib 
 from 
 -- collaborateurs, 
 -- metiers , 
 -- fonction , 
 £spaces£ £/spaces£competences_tech , 
 £spaces£ £/spaces£type_ct , 
 £spaces£ £/spaces£li_col_ct_fct , 
 -- li_col_met , 
 -- li_col_fct , 
 £spaces£ £/spaces£ 
 £spaces£ £/spaces£LI_MET_TYP_CT, 
 
 £spaces£ £/spaces£collaborateurs left outer join li_col_met on (collaborateurs.col_id=li_col_met.col_id) 
 £spaces£ £/spaces£left outer join li_col_fct on (collaborateurs.col_id=li_col_fct.col_id) 
 £spaces£ £/spaces£left outer join service on (collaborateurs.ser_id = service.ser_id), 
 £spaces£ £/spaces£li_col_fct left outer join fonction on (li_col_fct.fct_id=fonction.fct_id), 
 £spaces£ £/spaces£li_col_met left outer join metiers on (li_col_met.met_id=metiers.met_id) 
 where 
 -- li_col_met.met_id=metiers.met_id(+)) 
 £spaces£ £/spaces£LI_MET_TYP_CT.typ_id=type_ct.typ_id 
 -- and (collaborateurs.col_id=li_col_fc.col_id(+) 
 -- and li_col_fc.fct_id=fonction.fct_id(+)) 
 £spaces£ £/spaces£and (li_col_ct_fct.col_id=collaborateurs.col_id and li_col_ct_fct.met_id=metiers.met_id and competences_tech.ct_id=li_col_ct_fct.ct_id) and 
 £spaces£ £/spaces£(LI_MET_TYP_CT.typ_id=type_ct.typ_id) 
 -- and (collaborateurs.ser_id = service.ser_id(+)) 
 £spaces£ £/spaces£and (LI_MET_TYP_CT.ct_id = competences_tech.ct_id) 
 £spaces£ £/spaces£and (LI_MET_TYP_CT.met_id = li_col_met.met_id) 
 order by collaborateurs.col_nom asc , fonction.fct_lib asc , type_ct.typ_lib asc , LI_MET_TYP_CT.ct_valide desc, competences_tech.ct_lib asc; 
 
J'ai volontairement laissé en commentaires certaines lignes pour la clarté (il s'agit des lignes ayant été réécrites). 
 
Le problème c'est que Pgsql (et tout autre SGBD sql92 et +) me crache : 
`"ERROR: table name "li_col_fct" specified more than once"` 
 
Ce qui est effectivement vrai car j'utilise la table `li_col_fct` comme table droite d'une première jointure, puis comme table gauche d'une autre jointure : 
 
 [collaborateurs] left outer join li_col_fct on (collaborateurs.col_id=li_col_fct.col_id) 
et 
 li_col_fct left outer join fonction on (li_col_fct.fct_id=fonction.fct_id) 
 
Comment puis je me sortir de ce mauvais pas ? 
 
Merci d'avance !
</div><div class="naked_ctrl">
<form action="/index.cgi/contrast" method="get" name="gate">
<p><a href="http://altstyle.alfasado.net">AltStyle</a> によって変換されたページ <a href="httpS://linuxfr.org/forums/programmation-sql/posts/probl%C3%A8me-de-jointure-multiple.md">(-&gt;オリジナル)</a>
/ <label>アドレス: <input type="text" name="naked_post_url" value="httpS://linuxfr.org/forums/programmation-sql/posts/probl%C3%A8me-de-jointure-multiple.md" size="22" /></label> <label>モード: <select name="naked_post_mode">
<option value="default">デフォルト</option>
<option value="speech">音声ブラウザ</option>
<option value="ruby">ルビ付き</option>
<option value="contrast" selected="selected">配色反転</option>
<option value="larger-text">文字拡大</option>
<option value="mobile">モバイル</option>
</select>
<input type="submit" value="表示" />
</p>
</form>
</div>