Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 1E0019FC8C6; Tue, 5 Jun 2007 10:28:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80980-02-2; Tue, 5 Jun 2007 10:28:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id F32EC9FC6D0; Tue, 5 Jun 2007 10:21:46 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id DD4C375827; Tue, 5 Jun 2007 14:21:44 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 489CC2740D8; Tue, 5 Jun 2007 14:22:36 +0100 (BST) Message-ID: <46656368.7080504@archonet.com> Date: Tue, 05 Jun 2007 14:21:44 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Loredana Curugiu Cc: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Re: JOIN References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <4665537A.2060601@archonet.com> <1c23c8e70706050518v19c56d21j873985125b930db@mail.gmail.com> <46655981.4050403@archonet.com> <1c23c8e70706050559n60615ba7kab11f6f0f402d9ec@mail.gmail.com> In-Reply-To: <1c23c8e70706050559n60615ba7kab11f6f0f402d9ec@mail.gmail.com> Content-Type: multipart/mixed; boundary="------------070601060801060907030708" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/34 X-Sequence-Number: 28524 This is a multi-part message in MIME format. --------------070601060801060907030708 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Loredana Curugiu wrote: >>> You don't actually say what's wrong. What are you expecting as output? > > I should obtain the following result: [snip] Well, I've attached a test script using your example data and a copy of my results. Nothing leaping out as wrong here. It's entirely possible I've not had enough coffee today though and I'm missing something staring me in the face... -- Richard Huxton Archonet Ltd --------------070601060801060907030708 Content-Type: text/plain; name="test_results.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="test_results.txt" sum | theme | receiver | date | dates -----+-------+--------------+------------------------+-------------------------------------------------------------------------------- 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 2 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 3 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 18 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 4 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 6 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 10 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} (8 rows) count | theme | receiver | date | dates -------+-------+--------------+------------------------+-------------------------------------------------------------------------------- 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} (40 rows) --------------070601060801060907030708 Content-Type: text/x-sql; name="zzz_test.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="zzz_test.sql" /* CREATE TABLE loredana_test ( count int4, theme text, receiver text, "date" timestamptz, dates date[] ); COPY loredana_test FROM stdin WITH DELIMITER '|'; 2|LIA|+40741775621|2007-06-02 00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 3|CRIS|+40741775622|2007-06-01 00:00:00+00|{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 2|LIA|+40741775621|2007-06-03 00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 1|CRIS|+40741775622|2007-06-03 00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 4|LIA|+40741775621|2007-06-01 00:00:00+00|{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 1|CRIS|+40741775622|2007-06-02 00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} \. */ -- Uncomment one of these to control whether any rows match SET timezone = 'GMT'; -- SET timezone = 'GB'; -- Original query, but with the "date" column displayed too SELECT SUM(A.count), A.theme, A.receiver, A.date, A.dates FROM loredana_test A INNER JOIN loredana_test B ON A.theme=B.theme AND A.receiver=B.receiver AND A.date = ANY(B.dates) GROUP BY A.theme,A.receiver,A.date,A.dates; -- Query to test matches SELECT A.count, A.theme, A.receiver, A.date, B.dates FROM loredana_test A INNER JOIN loredana_test B ON A.theme=B.theme AND A.receiver=B.receiver AND A.date = ANY(B.dates) ORDER BY A.theme, A.receiver, A.date ; --------------070601060801060907030708--