X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id DF9DD9FB2C2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 22 Aug 2006 13:47:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 86935-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 22 Aug 2006 16:46:46 +0000 (UTC)
X-Greylist: delayed 00:06:39.877896 by SQLgrey-
Received: from web39806.mail.mud.yahoo.com (web39806.mail.mud.yahoo.com
	[209.191.106.67])
	by postgresql.org (Postfix) with SMTP id B81D19FA332
	for <pgsql-sql@postgresql.org>; Tue, 22 Aug 2006 13:46:44 -0300 (ADT)
Received: (qmail 88037 invoked by uid 60001); 22 Aug 2006 16:40:04 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=TUFxlyMd53kRZDyurHMQJlOYhtaqbIIri2zKetFQdaqf08VXgepj/uEOadBEFeGckzfXOodEyeehrReNYTguGPd1Kf2GR0MZUPeZnr5CO/BLb2PzSXC1OmIRnYbwCBzM13n3zSkja1fu7juwGyta4BuQ32PiwfZTAxNFvQsexxA=
	;
Message-ID: <20060822164004.88034.qmail@web39806.mail.mud.yahoo.com>
Received: from [64.94.69.222] by web39806.mail.mud.yahoo.com via HTTP;
	Tue, 22 Aug 2006 09:40:04 PDT
Date: Tue, 22 Aug 2006 09:40:04 -0700 (PDT)
From: Brian Cox <bcox4982@yahoo.com>
Subject: joining VIEWs
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1256817354-1156264804=:86311"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=3.817 tagged_above=0 required=5 tests=HTML_MESSAGE,
	SPF_HELO_SOFTFAIL, SPF_SOFTFAIL
X-Spam-Level: ***
X-Archive-Number: 200608/155
X-Sequence-Number: 26113

--0-1256817354-1156264804=:86311
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Given a view like:
 
 create view view1 as
 select g.id as UserGroupId, s.uid as UserId, s.time as StartTime from stats s join groups g on g.uid = s.uid
 
 and a SELECT like:
 
 select a.UserGroupId,b.UserGroupId from view1 a
 full outer join view1 b on b.UserGroupId = a.UserGroupId
 WHERE a.StartTime >= '2006-1-1' AND a.StartTime < '2007-1-1'
   AND b.StartTime >= '2005-1-1' AND b.StartTime < '2006-1-1';
 
 where there are 5695 rows in 2006 and 1 row in 2005, I expected to get a result set of 5695 rows, but instead got only 1 row (the common row in the 2 years).  This seems contrary to the definition of "full outer join".  Am I missing something?
 
 Thanks,
 Brian Cox
 		
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates starting at 1¢/min.
--0-1256817354-1156264804=:86311
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<font size="2">Given a view like:<br> <br> create view view1 as<br> select g.id as UserGroupId, s.uid as UserId, s.time as StartTime from stats s join groups g on g.uid = s.uid<br> <br> and a SELECT like:<br> <br> select a.UserGroupId,b.UserGroupId from view1 a<br> full outer join view1 b on b.UserGroupId = a.UserGroupId<br> WHERE a.StartTime &gt;= '2006-1-1' AND a.StartTime &lt; '2007-1-1'<br> &nbsp; AND b.StartTime &gt;= '2005-1-1' AND b.StartTime &lt; '2006-1-1';<br> <br> where there are 5695 rows in 2006 and 1 row in 2005, I expected to get a result set of 5695 rows, but instead got only 1 row (the common row in the 2 years).&nbsp; This seems contrary to the definition of "full outer join".&nbsp; Am I missing something?<br> <br> Thanks,<br> Brian Cox</font><p>&#32;
		<hr size=1>Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. <a href="http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com"> Great rates starting at 1¢/min.
--0-1256817354-1156264804=:86311--