Обсуждение: Left join?

Hi,

In the following table, codsol, codate and codfec are foreign keys
referencing table func and I need some help to codify a  SELECT command that
produces the following result set but instead of codsol, codate and codfec I
need the respectives names (column nome from table func).

postgres=# select * from reqtran;codreq | codsol | codate | codfec
--------+--------+--------+--------     1 |        |        |     2 |      1 |        |     3 |      1 |      1 |     4
|     1 |      1 |      1
 
(4 rows)

Thanks in advance,

Carlos

__________________________________________________
Table definitions:

postgres=# \d func    Table "public.func"Column |  Type   | Modifiers
--------+---------+-----------codfun | integer | not nullnome   | text    |
Indexes:   "func_pkey" PRIMARY KEY, btree (codfun)

postgres=# \d reqtran   Table "public.reqtran"Column |  Type   | Modifiers
--------+---------+-----------codreq | integer | not nullcodsol | integer |codate | integer |codfec | integer |
Indexes:   "reqtran_pkey" PRIMARY KEY, btree (codreq)
Foreign-key constraints:   "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)   "reqtran_codfec_fkey"
FOREIGNKEY (codfec) REFERENCES func(codfun)   "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)
 
__________________________________________________
Table contents:

postgres=# select * from func;codfun | nome
--------+-------     1 | nome1     2 | nome2     3 | nome3
(3 rows)

postgres=# select * from reqtran;codreq | codsol | codate | codfec
--------+--------+--------+--------     1 |        |        |     2 |      1 |        |     3 |      1 |      1 |     4
|     1 |      1 |      1
 
(4 rows)

> In the following table, codsol, codate and codfec are foreign keys
> referencing table func and I need some help to codify a  SELECT command that
> produces the following result set but instead of codsol, codate and codfec I
> need the respectives names (column nome from table func).
> 
> postgres=# select * from reqtran;
>  codreq | codsol | codate | codfec
> --------+--------+--------+--------
>       1 |        |        |
>       2 |      1 |        |
>       3 |      1 |      1 |
>       4 |      1 |      1 |      1
> postgres=# \d func

>      Table "public.func"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  codfun | integer | not null
>  nome   | text    |

> Indexes:
>     "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)
>     "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun)
>     "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)

Would this do what you need?
select R1.codreq,       CS.nome,       CD.nome,       CF.nome
from rectran as R1   left join func as CS on (R1.codsol=CS.codefun)   left join func as CD on (R1.codate=CD.codefun)
leftjoin func as CF on (R1.codfec=CF.codefun)
 
;

Regards,

Richard Broersma Jr.

It´s just want I need! Perfect! Thanks!

Carlos

> -----Mensagem original-----
> De: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
> Enviada em: sábado, 1 de julho de 2006 18:45
> Para: carlosreimer@terra.com.br; pgsql-sql@postgresql.org
> Assunto: Re: [SQL] Left join?
>
>
> > In the following table, codsol, codate and codfec are foreign keys
> > referencing table func and I need some help to codify a  SELECT
> command that
> > produces the following result set but instead of codsol, codate
> and codfec I
> > need the respectives names (column nome from table func).
> >
> > postgres=# select * from reqtran;
> >  codreq | codsol | codate | codfec
> > --------+--------+--------+--------
> >       1 |        |        |
> >       2 |      1 |        |
> >       3 |      1 |      1 |
> >       4 |      1 |      1 |      1
> > postgres=# \d func
>
> >      Table "public.func"
> >  Column |  Type   | Modifiers
> > --------+---------+-----------
> >  codfun | integer | not null
> >  nome   | text    |
>
> > Indexes:
> >     "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)
> >     "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun)
> >     "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)
>
> Would this do what you need?
>
> select R1.codreq,
>        CS.nome,
>        CD.nome,
>        CF.nome
> from rectran as R1
>    left join func as CS on (R1.codsol=CS.codefun)
>    left join func as CD on (R1.codate=CD.codefun)
>    left join func as CF on (R1.codfec=CF.codefun)
> ;
>
> Regards,
>
> Richard Broersma Jr.
>