Message-ID: <1124312109.4303a42d88b93@webmail.telus.net>
Date: Wed, 17 Aug 2005 13:55:09 -0700
From: Mischa Sandberg <mischa.sandberg@telus.net>
To: pgsql-sql@postgresql.org
Subject: Re: Is it This Join Condition Do-Able?  Ooh, ouch, blush
References: <F18A6F7CF1661F46920F2CF713122FED46CC6D@mail.aveo.aveopharma.com>
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CC6D@mail.aveo.aveopharma.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs

The Subject says it all. (author beats a hasty retreat).

Quoting Dmitri Bichko <dbichko@aveopharma.com>:

> I don't see what the problem is.
> Did you mean to insert (3,'C3') into table c, rather than b?

> > create temp table a(keyf int, val text);
> > create temp table b(keyf int, val text);
> > create temp table c(keyf int, val text);
> > insert into a values(1, 'A1');
> > insert into a values(2, 'A2');
> > insert into a values(3, 'A3');
> > insert into a values(4, 'A4');
> > insert into b values(1, 'B1');
> > insert into b values(2, 'B2');
> > insert into c values(2, 'C2');
> > insert into b values(3, 'C3');
> > select keyf, a.val as aval,
> >         coalesce(b.val,'Bxx') as bval,
> >         coalesce(c.val,'Cxx') as cval
> > from a left join b using(keyf) left join c using (keyf);
> > keyf aval bval cval
> > ---- ---- ---- ----
> >    1 A1   B1   Cxx
> >    2 A2   B2   C2
> >    3 A3   C3   Cxx
> >    4 A4   Bxx  Cxx