[SQL] join help

[Kashmir]

 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;2009040814;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;2009040814;13322;2131

many tia!
-k


  

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] join help

[Kashmir]

thx for the help!
this did it then for an individual collection and its fast :-):

SELECT
OneM.f_timestamp,
FiveM.f_ds,
FiveM.f_us,
OneM.f_ds,
OneM.f_us
FROM td_fetch1m_by_rrd_id OneM
left join 
(select f_rrd_id, f_ds, f_us, f_timestamp from td_fetch_by_rrd_id where 
f_rrd_id = 444) FiveM
ON (OneM.f_timestamp = FiveM.f_timestamp)
where OneM.f_rrd_id = 444
ORDER BY OneM.f_timestamp;








From: Justin 
To: Kashmir 
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, April 8, 2009 9:38:22 PM
Subject: Re: [SQL] join help

 Kashmir wrote: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...
  
The sql is joining on a time stamp??Using the time stamp i would expect odd 
ball results because a several unique f_rr_id could have the same timestamp 
especially if its heavy write table .

every 5th set only   What does this mean what makes something the 5th set.  
 

I normally avoid table aliasing  but these names i'm having a real tough time 
reading so we are going to use 1Minute = td_fetch1m_by_rrd_id and the 5Minute = 
td_fetch_by_rrd_id from here on out. 

You want to join whats in the 1Minute table to whats in the 5Minute only if it 
is in the 5Minute table and only return from 1Minute table where the timestamps 
is in the 5Minute table   If my understanding is correct this will work minus 
any typos.  To create a join condition we need a composite identity to join on. 
 So what i did is cast F_rr_id and F_timestamp to text adding them together to 
create a unique condition to join on.  

Also there is a typo above noted in bold f_ds is listed twice i believe that is 
a mistake. 

SELECT
OneM.f_timestamp,
FiveM.f_ds,
FiveM.f_us,
OneM.f_ds,
OneM.f_us
FROM td_fetch1m_by_rrd_id OneM,
left Join (select f_rrd_id, f_ds, f_us, f_timestamp
from td_fetch_by_rrd_id ) FiveM
ON (OneM.f_rrd_id::text || OneM.f_timestamp::text) = 
(FiveM.f_rrd_id::text || FiveM.f_timestamp::text)
ORDER BY OneM.f_timestamp;