Re: [SQL] OUTER JOINs in PostgreSQL

On Sat, 5 Jun 1999, Ant9000 wrote:

> On Wed, 02 Jun 1999, you wrote:
> >I think that you are looking for something like:
> >
> >SELECT m.id, m.name, d.lastvisit, d.info
> >       FROM master m, detail d
> >       WHERE m.id = d.id;
> >
> >Jason
> >
> 
> No, that way I get an inner join: in my example the result would be
> 
> id | name  | lastvisit | info
> -----------------------------
> 1  | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1  | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
> 
> and I'm instead looking for this output:
> 
> id | name    | lastvisit | info
>  -----------------------------
> 1  | Alpha   | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1  | Alpha   | Wed Jun 02 19:45:08 1999 CEST | some more blah's
> 2  | Beta     |                                                 |
> 3  | Gamma |                                                 |
> 
Try this:

SELECT m.id, m.name, d.lastvisit, d.info      FROM master m, detail d      WHERE m.id = d.id
UNION ALL
SELECT m.id, m.name, 0, 0      FROM master m      WHERE not m.id=ANY(select id from detail);