Board index PostgreSQL

SELECT with LEFT OUTER JOIN ON

SELECT with LEFT OUTER JOIN ON

Post by Laurent Pature » Wed, 20 Feb 2002 19:25:22



<html>
<br>
Hi,<br>
my query is :<br><br>
CREATE VIEW &quot;vue_formation&quot; 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_3 AS 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=3Du.id <br>
AND f.unite_duree_totale=3Du1.id) <br>
ORDER BY intitule<br><br>
There's a mistake near the ON, I don't find any example using join
condition on postgres.org/idocs<br>
Is anybody could help me (correct syntax or link with example).<br>
I find the doc on it not really complete.<br><br>
rgds,<br><br>
<br><br>
<x-sigsep><p></x-sigsep>
Laurent Patureau.<br>
____________________________________<br>
<font size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;
Laurent Patureau<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;
</font><font size=3D1>S.A.R.L.</font><font size=3D2> ID.fr<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;

dfr.net</a><br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href=3D"http://www.idfr.net/" eudora=3D"autourl">http://www.idfr.net</a>=
<br><br>
&nbsp;&nbsp;&nbsp; Porter un nouveau regard =E0 votre audience :<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href=3D"http://www.wysistat.com/" eudora=3D"autourl">http://www.wysistat=
.com</a><br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Tel : 03 81 48 03 13<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Fax : 03 81 48 04 83<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,
Boulevard Winston Churchill<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
25 000 Besan=E7on<br>
___________________________________</font></html>
 
 
 
Top

SELECT with LEFT OUTER JOIN ON

Post by Luis Sou » Wed, 20 Feb 2002 20:15:21


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 
Top

SELECT with LEFT OUTER JOIN ON

Post by Luis Sou » Wed, 20 Feb 2002 21:24:30



> 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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 
Top

1. Multiple LEFT OUTER JOIN Syntax?

Hello Everyone,

I am creating an ODBC query to an Access database where the first table
(Listings)
has records for every Listings.ID, but each of the 2 tables I need to join
to it (Addendums and ListingMisc ) may or may not have records with a
matching value in their corresponding field.  The following statements work
as expected for one or the other join:

cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
Addendums.Addendum FROM
Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)

or

cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
(Listings.ID = ListingMisc.ListingID)

oServer1 := SQLSelect{ cSelect, oConn1 }
oServer1:Execute( cSelect )

The above Execute works correctly for either of the cSelect values listed.

How do I combine the Joins?  The MS ODBC help file says that both lists and
nested outer joins are supported, but I can't find samples of them and all
my attempts have returned syntax errors either at the FROM level or the JOIN
level.

Help???

Lynn C. Ormond
Lucero Research

2. New domain problem

3. LEFT OUTER JOIN how to use?

4. Reading MS-DOS CDs on my A3000

5. LEFT OUTER JOIN & inheritance

6. modified sine wave inverter OK for PB540?

7. LEFT OUTER JOIN and Update()

8. significance of signal code?????

9. Left Outer Join of Views?

10. Left Outer Join Syntax

11. LEFT OUTER JOIN problem

12. Left Outer Join Question

13. Left outer join with WHERE clause?


All times are UTC
Board index