Skip site navigation (1) Skip section navigation (2)

Re: Full Outer Join with dates as index?

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Bruche,ME (pgr)" <M(dot)E(dot)Bruche(at)lse(dot)ac(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Full Outer Join with dates as index?
Date: 2003-10-07 18:55:19
Message-ID: 20031007115112.J53418@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 7 Oct 2003, Bruche,ME (pgr) wrote:

> I need to do something like a full outer join on two tables that contain
> dates and prices that were observed on these dates (call them the prices
> of apples and oranges), but I want only one column of dates. On some
> days, the price of apples is observed but the price of oranges is not
> observed, and vice versa. The tables look something like this:

If the columns do have the same names, I think the easiest way is
something like:
select * from price_of_oranges FULL OUTER JOIN price_of_apples USING
(date)
which should mosh the two date columns together in the way you want.

If not, you could probably use something like:
select coalesce(a.date1,b.date2), a.price_of_oranges,b.price_of_apples
from price_of_oranges a full outer join price_of_apples b on
(a.date1=b.date2)

In response to

pgsql-novice by date

Next:From: Louise CofieldDate: 2003-10-07 19:05:02
Subject: Re: Copy table structure
Previous:From: sibusiso xoloDate: 2003-10-07 18:04:56
Subject: problems retrieving images in 7.3.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group