EXTERN JOIN with WHEN query

Subject: Re: [GENERAL] EXTERN JOIN with WHEN query
Date: Friday 06 June 2003 10:57
From: Darko Prenosil <darko.prenosil@finteh.hr>
To: javier garcia - CEBAS <rn001@cebas.csic.es>

On Thursday 05 June 2003 16:56, 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:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha);

This is perfect. But to make it better, would like to include just the
dates from the first one in the precal table. So, I've tried:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);

With the answer:

ERROR: parser: parse error at or near "WHEN"

Could you help me with this query?

Maybe WHERE instead of WHEN ?

-------------------------------------------------------

On Thu, 5 Jun 2003 16:56:54 +0200, javier garcia - CEBAS
<rn001@cebas.csic.es> wrote:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);

With the answer:

ERROR: parser: parse error at or near "WHEN"

The keyword is "WHERE". And I guess the WHERE clause has to be

WHERE fechas.fecha >= (SELECT min(fecha) FROM precal41)

or, if there is an index on precal41.fecha, more efficiently but
non-standard

WHERE fechas.fecha >= (SELECT fecha
FROM precal41 ORDER BY fecha LIMIT 1)

HTH.
Servus
Manfred

On Thursday 05 Jun 2003 3:56 pm, javier garcia - CEBAS wrote:

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:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha);

This is perfect. But to make it better, would like to include just the
dates from the first one in the precal table. So, I've tried:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);

Perhaps something like (not tested):

SELECT .... WHERE fechas.fecha >= (SELECT min(precal41.fecha) FROM precal41);
--
Richard Huxton

On Thu, Jun 05, 2003 at 16:56:54 +0200,
javier garcia - CEBAS <rn001@cebas.csic.es> wrote:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
(fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);

With the answer:

ERROR: parser: parse error at or near "WHEN"

Could you help me with this query?

The keyword should be "WHERE" instead of "WHEN".

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.