Обсуждение: SELECT with LEFT OUTER JOIN ON

<br /> Hi,<br /> my query is :<br /><br /> CREATE VIEW "vue_formation" AS SELECT <br /> f.numero_formation, <br />
f.intitule_1|| ' ' || f.intitule_2 || ' ' || f.intitule_3 AS intitule, <br /> f.convention, <br /> f.subvention, <br />
f.effectif_conv,<br /> f.eff_subv, <br /> f.rythme_1 || ' ' || f.rythme_2 || ' ' || f.rythme_3 AS rythme, <br />
f.duree_entreprise|| ' ' || u.nom || ' ' || f.commentaire_duree AS duree_entreprise, <br /> f.public_1 || ' ' ||
f.public_2|| ' ' || f.public_3 AS public, <br /> f.admission_1 AS admission_1, <br /> f.admission_2 || ' ' ||
f.admission_3AS admission_2, <br /> f.unknown_3 AS ref_admission, <br /> f.statut AS statut, <br /> f.duree_totale || '
'||u1.nom AS duree_totale <br /> FROM formation f, ref_unite_de_duree u, ref_unite_de_duree u1<br /> LEFT OUTER JOIN ON
(f.unite_duree_entreprise=u.id<br /> AND f.unite_duree_totale=u1.id) <br /> ORDER BY intitule<br /><br /> There's a
mistakenear the ON, I don't find any example using join condition on postgres.org/idocs<br /> Is anybody could help me
(correctsyntax or link with example).<br /> I find the doc on it not really complete.<br /><br /> rgds,<br /><br /><br
/><br/><p> Laurent Patureau.<br /> ____________________________________<br /><font size="2">                       
LaurentPatureau<br />                              </font><font size="1">S.A.R.L.</font><font size="2"> ID.fr<br /><br
/>                <a eudora="autourl" href="mailto:lpatureau@idfr.net">mailto:lpatureau@idfr.net</a><br /><br />
                     <a eudora="autourl" href="http://www.idfr.net/">http://www.idfr.net</a><br /><br />     Porter un
nouveauregard à votre audience :<br />                    <a eudora="autourl"
href="http://www.wysistat.com/">http://www.wysistat.com</a><br/><br />                      Tel : 03 81 48 03 13<br />
                    Fax : 03 81 48 04 83<br /><br />            16, Boulevard Winston Churchill<br />
                     25 000 Besançon<br /> ___________________________________</font> 

Instead of:

FROM formation f, ref_unite_de_duree u, ref_unite_de_duree u1
LEFT OUTER JOIN ON (f.unite_duree_entreprise=u.id
AND f.unite_duree_totale=u1.id)

try:

FROM formation f LEFT OUTER JOIN ref_unite_de_duree u ON 
(f.unite_duree_entreprise=u.id)       LEFT OUTER JOIN ref_unite_de_duree u1 ON 
(f.unite_duree_entreprise=u1.id)

I don't know if the results are that you expect but the sintax, I think 
is correct !

Best regards, Luis Sousa

Luis Sousa wrote:

> Instead of:
>
> FROM formation f, ref_unite_de_duree u, ref_unite_de_duree u1
> LEFT OUTER JOIN ON (f.unite_duree_entreprise=u.id
> AND f.unite_duree_totale=u1.id)
>
> try:
>
> FROM formation f LEFT OUTER JOIN ref_unite_de_duree u ON 
> (f.unite_duree_entreprise=u.id)
>        LEFT OUTER JOIN ref_unite_de_duree u1 ON 
> (f.unite_duree_entreprise=u1.id)
>
> I don't know if the results are that you expect but the sintax, I 
> think is correct !
>
> Best regards, Luis Sousa
>
Sorry, correction:

try:
FROM formation f LEFT OUTER JOIN ref_unite_de_duree u ON 
(f.unite_duree_entreprise) = (u.id)      LEFT OUTER JOIN ref_unite_de_duree u1 ON 
(f.unite_duree_entreprise) =(u1.id)