Thread: 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. >