From: Kashmir <kashmir_us_1999@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: join help
Date: Wed, 8 Apr 2009 13:17:21 -0700 (PDT)
Message-ID: <411904.26957.qm@web31905.mail.mud.yahoo.com> (raw)
i'm in need of some psql advise,
believe its rather a trivial issue, but confusing for me...
facing following issue:
got 2 tables like:
CREATE TABLE td_fetch_by_rrd_id (
f_rrd_id numeric NOT NULL,
f_timestamp numeric NOT NULL,
f_ds numeric,
f_us numeric,
CONSTRAINT td_fetch_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp)
)
and:
CREATE TABLE td_fetch1m_by_rrd_id (
f_rrd_id numeric NOT NULL,
f_timestamp numeric NOT NULL,
f_ds numeric,
f_us numeric,
CONSTRAINT td_fetch1m_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp)
)
only difference is:
first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute.
I
want to run a query that would return for the same 'f_rrd_id' all
values from both tables sorted by f_timestamp, of course a set would
only have values from the 5m table if the timestamp was present there
too (every 5th set only)
being a sql-lamer, i used some query
builder help to build my query (which served me quite well in the past
for all my 'complicated' sqls), and was suggested for f_rrd_id=444 to
use something as:
SELECT
td_fetch1m_by_rrd_id.f_timestamp,
td_fetch_by_rrd_id.f_ds,
td_fetch_by_rrd_id.f_ds,
td_fetch1m_by_rrd_id.f_ds,
td_fetch1m_by_rrd_id.f_us
FROM td_fetch_by_rrd_id
RIGHT JOIN td_fetch1m_by_rrd_id ON td_fetch_by_rrd_id.f_timestamp=td_fetch1m_by_rrd_id.f_timestamp
WHERE td_fetch1m_by_rrd_id.f_rrd_id=444
ORDER BY td_fetch1m_by_rrd_id.f_timestamp;
and this works quite fine and as expected in the source env (some gui-sqler).
but when i take this into psql, i get totally messed up results, the values just dont make any sense...
assume it is a simple 'shoot the monkey messing with sql' type of an issue, and was hoping real sqlers could help out quickly?
:)
an example set in the 1m table would look like:
444;20090408135500;15049;3898
444;20090408135600;11760;1023
444;20090408135700;21956;13913
444;20090408135800;14313;3427
444;20090408135900;12876;1007
444;20090408140000;13307;2101
444;20090408140100;25905;5611
and the other table would only have every 5th ts matching,
with minor diffs in the f_us/f_ds columns, e.g. like:
444;20090408135500;15054;3958
444;20090408140000;13322;2131
many tia!
-k
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kashmir_us_1999@yahoo.com
Subject: Re: join help
In-Reply-To: <411904.26957.qm@web31905.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox