Thread: OUTER JOINS in 6.4.2?? Work around??

Greetings,

I know this is probably asked a thousand times, but I cannot find a
reference anywhere on the web site.  If there is one, please point me there...

Is it possible to do LEFT and RIGHT OUTER JOINS?  i.e.

SELECT * FROM foo LEFT OUTER JOIN bar ON foo.id=bar.id;

All I could find on the web site was that PostgreSQL supports a subset of
SQL92 and SQL3.  Is there someplace that tells what that subset is?

Maybe I don't need the join if there is some other way...  I'm trying to
work out a query to give me one row from the many side of a 1-to-many
relationship for each row in the one side; basically making a 1-to-1 by
ignoring extra rows in the many table.  For instance, a table of customers
(the one table) have phone numbers (the many table).  I want to display a
list of customers with a phone number, but only one number regardless of
how many phone numbers there are.

Any insight would be greatly appreciated.

Thanks,
Matthew

Matthew Hagerty wrote:
>
> All I could find on the web site was that PostgreSQL supports a subset of
> SQL92 and SQL3.  Is there someplace that tells what that subset is?

This is a very important question for PostgreSQL as a whole. Does anyone
know what is missing to claim SQL92 compliance? I can get my higher-ups
to agree that we should adopt software based on it's conformance to
existing standards, to avoid the proprietary lock-in problem. This is
usually my strategy for getting Open Source software in ;-) since it
conforms so much better (usually)  When I discovered PostgrSQL is a
little short in that department, I've had to mumble and hand-wave a
little.

Some of us postgreSQL users could help the core developers a lot on
this. I'd love to see an extension of the DB comparison table on the web
page, with SQL standard features listed.

I realize the actual text of the SQL92 standard must be purchased, but
is there a 'bullet list' version anywhere? I can't seem to find one via
altavista.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

On Thu, 25 Feb 1999, Ross J. Reedstrom wrote:

> This is a very important question for PostgreSQL as a whole. Does anyone
> know what is missing to claim SQL92 compliance? I can get my higher-ups
> to agree that we should adopt software based on it's conformance to
> existing standards, to avoid the proprietary lock-in problem. This is
> usually my strategy for getting Open Source software in ;-) since it
> conforms so much better (usually)  When I discovered PostgrSQL is a
> little short in that department, I've had to mumble and hand-wave a
> little.

While it may take a little research to make a comprehensive listing, the
SQL command and keyword lists in the PostgreSQL Users' Manual list what
compatibility there is with SQL92 or SQL3, as well as what keywords are
available in PostgreSQL versus the standard.

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Research is what I'm doing when I don't know what I'm doing.
        -- Wernher von Braun

----- BEGIN GEEK CODE BLOCK -----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N- o K- w--- O@ M-@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ e>++ h+ r++ y++++
------ END GEEK CODE BLOCK ------

At 21:05 +0200 on 25/2/99, Ross J. Reedstrom wrote:


> I realize the actual text of the SQL92 standard must be purchased, but
> is there a 'bullet list' version anywhere? I can't seem to find one via
> altavista.

I actually downloaded it once. I am not sure it's the actual final text -
it says it's a draft from July 30, 1992, so my guess is that it's pretty
close to the final one.

It's a 1.5m plain text file. Not that I remember where I downloaded it, but
I suppose I can make it available on my website or something if anybody is
interested.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma