Re: EXTERN JOIN with WHEN query

On Thu, 5 Jun 2003, javier garcia - CEBAS wrote:

> Hi all;
> This is a query that I guess is not very difficult, but I'm a newbie;
> I've got a lot of tables, each of them with two columns:
>
> SELECT * FROM precal; ->
> (date) (real)
> fecha | precipitacion
> ------------+---------------
> 1996-01-01 | 0.6
> 1996-02-01 | 0.7
> ...
>
>
> But in this table there are some inexistents records (some missing days)
> And I would like to create lists with a full list of dates and corresponding
> precipitation data, with gaps when the row didn't exist.
> So; I've created a table with a complete series of dates from 1950 up to
> date, and made the query:

Any time you're gonna do this, you can make a table with all the dates in
it already, and left join against that.

I.e. if you want a report for every month, numbered 1 through 12, then
just create a table with an id 1 to 12 and the names of the months. Since
the table's so small, it doesn't even really need indexes.