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(dot)Steubesand(at)fh-trier(dot)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(dot)Steubesand(at)fh-trier(dot)de)
>
>

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