[SQL] 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 null nome | text| Indexes: "func_pkey" PRIMARY KEY, btree (codfun) postgres=# \d reqtran Table "public.reqtran" Column | Type | Modifiers +-+--- codreq | integer | not null codsol | 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" FOREIGN KEY (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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RES: [SQL] Left join?
It´s just want I need! Perfect! Thanks! Carlos > -Mensagem original- > De: Richard Broersma Jr [mailto:[EMAIL PROTECTED] > Enviada em: sábado, 1 de julho de 2006 18:45 > Para: [EMAIL PROTECTED]; 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. > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
