Re: Full Outer Join with dates as index?

Hi,

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:

price_of_oranges:
date price_of_oranges
1999-1-1 3.45
1999-1-3 4.65

price_of_apples:
date price_of_apples
1999-1-2 5.87

What I want is a table that looks like this:
date price_of_oranges price_of_apples
1999-1-1 3.45 NULL
1999-1-2 NULL 5.87
1999-1-3 5.87 NULL

What's the easiest way to do this?

Thanks.

Max

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)