Thread: some external sql not working in psql

i'm in need of some psql advise,
believe its rather a trivial issue, but confusing for me...
(and hope this is the correct list for this?)

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
coursea 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

Kashmir <kashmir_us_1999@yahoo.com> writes:
> 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;

Seems like that should be a LEFT JOIN, if you're expecting there to be
missing values in td_fetch_by_rrd_id rather than the other.  The WHERE
and ORDER BY clauses don't look right either unless LEFT was meant.

            regards, tom lane