Re: [ADMIN] Outer joins

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                           |
-------------------------------------------------------------------------