Outer joins

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

Outer joins

От:
T.Steubesand@fh-trier.de
Дата:
Hello,

is there a possibility to use "outer joins" (left outer join) with pgsql?
I have a problem joining two tables:

TablePerson						TableTitle
-----------						----------
pk_idperson	int primary key			pk_idtitle		int primary key
name		varchar(20)				title			varchar(10)
firstname	varchar(20)
fk_idtitle	int

INSERT INTO TableTitle VALUES (1,'Dr.');
INSERT INTO TableTitle VALUES (2,'Prof.');

INSERT INTO TablePerson VALUES (1,'Kohl','Helmut',1);
INSERT INTO TablePerson VALUES (2,'Steubesand','Thomas',NULL);
INSERT INTO TablePerson VALUES (3,'Smith','Peter',2);

SELECT name,firstname,title 
  FROM TablePerson,TableTitle
  WHERE TablePerson.fk_idtitle = TableTitle.pk_idtitle;

results:    Kohl		Helmut	Dr.
		Smith 	Peter		Prof.

How can I force pgsql to return the following result:
		Kohl		Helmut	Dr.
		Steubesand	Thomas
		Smith 	Peter		Prof.

Thank you

Thomas Steubesand
(T.Steubesand@fh-trier.de)

Re: [ADMIN] Outer joins

От:
Amos Hayes <ahayes@polkaroo.net>
Дата:
On Mon, 2 Nov 1998 T.Steubesand@fh-trier.de wrote:

> SELECT name,firstname,title 
>   FROM TablePerson,TableTitle
>   WHERE TablePerson.fk_idtitle = TableTitle.pk_idtitle;
> 
> results:    Kohl		Helmut	Dr.
> 		Smith 	Peter		Prof.
> 
> How can I force pgsql to return the following result:
> 		Kohl		Helmut	Dr.
> 		Steubesand	Thomas
> 		Smith 	Peter		Prof.

How about putting an entry like (3,'') into TableTitle and assigning an
fk_idtitle of 3 to people without a title.


--
Amos Hayes
ahayes@polkaroo.net			Ingenia Group - Software Kinetics Ltd.
http://polkaroo.net/~ahayes			http://www.sofkin.com

Re: [ADMIN] Outer joins

От:
Guido Weber <guido@castor.atlas.de>
Дата:
Try the following:

SELECT name,firstname,title
  FROM TablePerson,TableTitle
  WHERE TablePerson.fk_idtitle = TableTitle.pk_idtitle
UNION
SELECT name,firstname,''::varchar(10)
  FROM TablePerson
  WHERE fk_idtitle IS NULL;

Guido Weber

On Mon, 2 Nov 1998 T.Steubesand@fh-trier.de wrote:

> Hello,
> 
> is there a possibility to use "outer joins" (left outer join) with pgsql?
> I have a problem joining two tables:
> 
> TablePerson						TableTitle
> -----------						----------
> pk_idperson	int primary key			pk_idtitle		int primary key
> name		varchar(20)				title			varchar(10)
> firstname	varchar(20)
> fk_idtitle	int
> 
> INSERT INTO TableTitle VALUES (1,'Dr.');
> INSERT INTO TableTitle VALUES (2,'Prof.');
> 
> INSERT INTO TablePerson VALUES (1,'Kohl','Helmut',1);
> INSERT INTO TablePerson VALUES (2,'Steubesand','Thomas',NULL);
> INSERT INTO TablePerson VALUES (3,'Smith','Peter',2);
> 
> SELECT name,firstname,title 
>   FROM TablePerson,TableTitle
>   WHERE TablePerson.fk_idtitle = TableTitle.pk_idtitle;
> 
> results:    Kohl		Helmut	Dr.
> 		Smith 	Peter		Prof.
> 
> How can I force pgsql to return the following result:
> 		Kohl		Helmut	Dr.
> 		Steubesand	Thomas
> 		Smith 	Peter		Prof.
> 
> Thank you
> 
> Thomas Steubesand
> (T.Steubesand@fh-trier.de)
> 
> 

-------------------------------------------------------------------------
| Guido Weber                      |  STN Atlas Elektronik GmbH, SLE3   |
| Tel.: +49/421/457-4076           |  Sebaldsbruecker Heerstr. 235      |
| Fax :            -3578           |  D-28305 Bremen                    |
| email: weber.guido@stn-atlas.de  |  Germany                           |
-------------------------------------------------------------------------
FAQ