Re: LEFT OUTER JOIN?

Peter Landis wrote:
>
> Hi- I'm a newbie at postgres but have a pretty good
> understanding of SQL statements.  I have created two
> views and wanted to do a LEFT OUTER JOIN of the two
> tables.  The sytax is
>
> select * from vcompany LEFT OUTER JOIN ON
> vcompany.id=vregistry.id;
>
> I get the following error:
> LEFT OUTER JOIN not yet implemented
>
> My question is does postgresql 6.5 support Left out
> join and if not is there another way of implementing
> this sql statement to give me a LEFT OUTER JOIN.
>
> The logic if very simple.  Look below to see the
> tables:
>
> vcompany
> +--------+---------------+
> |    id     |      Name  |
> +-------+----------------+
> |     1     |      Peter |
> |     2     |      John  |
> |     3     |      Joe   |
> |     4     |      Jerry |
> |     5     |      Mike  |
> +--------+---------------+
>
> vcompany
> +--------+-------------+
> |    id     |      Desc|
> +-------+--------------+
> |     1     |      A   |
> |     2     |      B   |
> |     5     |      D   |
> +--------+-------------+

I assume you meant that the above is vregistry?

>
> JOIN OF THE TWO
>
> +--------+---------------+-------------+
> |    id  |      Name     |      Desc   |
> +-------+----------------+-------------+
> |   1    |      Peter    |      A      |
> |   2    |      John     |      B      |
> |   3    |      Joe      |             |
> |   4    |      Jerry    |             |
> |   5    |      Mike     |      D      |
> +--------+---------------+-------------+

Unfortunately, PostgreSQL as of version 7.0 does not yet have
left outer join. The traditional way to simulate this behavior is
as follows:

SELECT vcompany.id, vcompany.name, vregistry.desc
FROM vcompany, vregistry
WHERE vcompany.id = vregistry.id
UNION
SELECT vcompany.id, vcompany.name, NULL
FROM vcompany
WHERE NOT EXISTS
(SELECT vregistry.id WHERE vregistry.id = vcompany.id)
ORDER BY vcompany.id;

Hope that helps,

Mike Mascari