Thread: EXTERN JOIN with WHEN query
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? Thanks and regards Javier -------------------------------------------------------
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 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, 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
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, 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.