Обсуждение: Several left outer joins on a same table

Поиск
Список
Период
Сортировка

Several left outer joins on a same table

От
"Th Templ"
Дата:
Hello,

This is a problem about several left outer joins on a same table.

Here are my tables

Personne
------------
int lieu1
int lieu2

Lieu
------------
int lieu_id
varchar(50) lieu_nom

I want to have in a single request the name of lieu1 and/or lieu2 in the
'Lieu' table if there is a correspondance and if there are not null.
I see the postgresql documentation and I have no problem to make one left
outer join, but two??

Thans for your answers
Templ

_________________________________________________________________
Join the world�s largest e-mail service with MSN Hotmail.
http://www.hotmail.com

Re: Several left outer joins on a same table

От
Dave Cramer
Дата:
Templ,

What exactly is your question here? You can easily test your sql out in
psql. AFAIK, postgres should handle two outer joins. If you have access
to the source for the driver have a look at the getImportedExported keys
to see very complex sql selects that do multiple joins on the same
tables

Dave
On Tue, 2002-07-23 at 05:38, Th Templ wrote:
> Hello,
>
> This is a problem about several left outer joins on a same table.
>
> Here are my tables
>
> Personne
> ------------
> int lieu1
> int lieu2
>
> Lieu
> ------------
> int lieu_id
> varchar(50) lieu_nom
>
> I want to have in a single request the name of lieu1 and/or lieu2 in the
> 'Lieu' table if there is a correspondance and if there are not null.
> I see the postgresql documentation and I have no problem to make one left
> outer join, but two??
>
> Thans for your answers
> Templ
>
> _________________________________________________________________
> Join the world’s largest e-mail service with MSN Hotmail.
> http://www.hotmail.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

Re: Several left outer joins on a same table

От
Aaron Mulder
Дата:
    Try something like:

select l1.lieu_nom, l2.lieu_num
  from Personne p left outer join Lieu l1 on lieu_id=lieu1
       left outer join Lieu l2 on lieu_id=lieu2

Aaron

On Tue, 23 Jul 2002, Th Templ wrote:
> Here are my tables
>
> Personne
> ------------
> int lieu1
> int lieu2
>
> Lieu
> ------------
> int lieu_id
> varchar(50) lieu_nom
>
> I want to have in a single request the name of lieu1 and/or lieu2 in the
> 'Lieu' table if there is a correspondance and if there are not null.
> I see the postgresql documentation and I have no problem to make one left
> outer join, but two??
>
> Thans for your answers
> Templ
>
> _________________________________________________________________
> Join the world’s largest e-mail service with MSN Hotmail.
> http://www.hotmail.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: Several left outer joins on a same table

От
Meder Bakirov
Дата:
В письме от 23 Июль 2002 15:38 Th Templ написал(а):
> Hello,
>
> This is a problem about several left outer joins on a same table.
>
> Here are my tables
>
> Personne
> ------------
> int lieu1
> int lieu2
>
> Lieu
> ------------
> int lieu_id
> varchar(50) lieu_nom
>
> I want to have in a single request the name of lieu1 and/or lieu2 in the
> 'Lieu' table if there is a correspondance and if there are not null.
> I see the postgresql documentation and I have no problem to make one left
> outer join, but two??
>
> Thans for your answers
> Templ
>
Look at NATURAL [FULL] OUTER JOIN

> _________________________________________________________________
> Join the world▓s largest e-mail service with MSN Hotmail.
> http://www.hotmail.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html