Postgres Pro
Downloads
Home   >   mailing lists

Thread: Outer joins

Outer joins

From
T.Steubesand@fh-trier.de
Date:
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

From
Guido Weber
Date:
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                           |
-------------------------------------------------------------------------

Re: [ADMIN] Outer joins

From
Amos Hayes
Date:
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