Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5E1C7633F32
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 22:38:27 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 42926-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 22:38:25 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp122.sbc.mail.re3.yahoo.com (smtp122.sbc.mail.re3.yahoo.com
	[66.196.96.95])
	by mail.postgresql.org (Postfix) with SMTP id C346A633E9C
	for <pgsql-sql@postgresql.org>; Wed,  8 Apr 2009 22:38:25 -0300 (ADT)
Received: (qmail 38190 invoked from network); 9 Apr 2009 01:38:23 -0000
Received: from unknown (HELO ?192.168.1.67?) (justin@99.14.96.182 with plain)
	by smtp122.sbc.mail.re3.yahoo.com with SMTP;
	9 Apr 2009 01:38:23 -0000
X-YMail-OSG: 
 1mjPE24VM1mZ8ta4TQ302XQ4gzc9x8YaFOIMyhFKbTIUu.K3ixkNvTi.lwawLoGX20FLIpqnfi..gJGYgx_mDWKFssAdKfM0TDkFJ32Hek9jOZ9yx_Z1Hrr0gYq3JmP50VLrUYVvgSsK594nJFBXQ8z2fV_syw68VU6JkJKzl23idjEB.bkZ3tXjIhhUqeUz2t3mHDpbuwU32Vr4DS2UyOVI.reAtpMIBFYyyb0sImHzbkQlMdbeVdMIX0_EQW.yH3xMRM9fCcsUqQ--
X-Yahoo-Newman-Property: ymail-3
Message-ID: <49DD518E.8000504@emproshunts.com>
Date: Wed, 08 Apr 2009 21:38:22 -0400
From: Justin <justin@emproshunts.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Kashmir <kashmir_us_1999@yahoo.com>
CC: pgsql-sql@postgresql.org
Subject: Re: join help
References: <411904.26957.qm@web31905.mail.mud.yahoo.com>
In-Reply-To: <411904.26957.qm@web31905.mail.mud.yahoo.com>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.673 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672
X-Spam-Level: *
X-Archive-Number: 200904/47
X-Sequence-Number: 32393

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Kashmir wrote:only difference is:<br>
<blockquote cite="mid:411904.26957.qm@web31905.mail.mud.yahoo.com"
 type="cite">
  <pre wrap="">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,
<u><b>td_fetch_by_rrd_id.f_ds,</b></u>
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...
  </pre>
</blockquote>
<pre wrap="">
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;
</pre>
</body>
</html>