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