DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type;
	b=mAz7Cy4YrPIZxcJJMjg5qW2a8PAWyr6vn0R7HaJTL51S78TFjnVuDOGAJ/MAPMt53NxW4PtzOwi4A54bz1Ps9wsAc8a9fPfrPWv5MVRRAB5bCNym4uqcklDyQeGmkEtbzaII7rI8ybUxoxyf3BaETNSGKxdx1eIBlETasjX5mlU=;
Message-ID: <411904.26957.qm@web31905.mail.mud.yahoo.com>
Date: Wed, 8 Apr 2009 13:17:21 -0700 (PDT)
From: Kashmir <kashmir_us_1999@yahoo.com>
Subject: join help
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


 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