Re: full join question... - Mailing list pgsql-general

"Jonas F. Henriksen" <jonas.f.henriksen@imr.no> writes:
> Well, the problem is I want the result on one row for each depth, so it
> will fit nicely into a table, like this:

> depth measuretype1_value measuretype2_value
> 10    1.78    2.55
> 20    2.12
> 30        3.12
> 40    1.3    1.4

I think what you have to do is filter the rows according to measuretype
*before* you join them.  Something like

select * from
  (select depth, value as measuretype1_value from testtable
   where measuretype = 1040) ss1
  full join
  (select depth, value as measuretype2_value from testtable
   where measuretype = 4001) ss2
  using (depth);

            regards, tom lane