Thread: LEFT OUTER JOIN?

LEFT OUTER JOIN?

From
Peter Landis
Date:
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   |
+--------+-------------+

JOIN OF THE TWO

+--------+---------------+-------------+
|    id  |      Name     |      Desc   |
+-------+----------------+-------------+
|   1    |      Peter    |      A      |
|   2    |      John     |      B      |
|   3    |      Joe      |           |
|   4    |      Jerry    |           |
|   5    |      Mike     |      D      |
+--------+---------------+-------------+

__________________________________________________
Do You Yahoo!?
Kick off your party with Yahoo! Invites.
http://invites.yahoo.com/

Re: LEFT OUTER JOIN?

From
Mike Mascari
Date:
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