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