Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 7B6769FB482 for ; Tue, 5 Jun 2007 11:47:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 53724-01-2 for ; Tue, 5 Jun 2007 11:46:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.245]) by postgresql.org (Postfix) with ESMTP id 8DD9D9FBDEB for ; Tue, 5 Jun 2007 11:46:45 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so399390ana for ; Tue, 05 Jun 2007 07:46:44 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KBW2q2aAjndLGnKt6X/oa3jWMN+myzJkcK9kMTyXcMVc+fiQn6/Ag303P9Qzs55czsPerSv+QFN+CQulbSR9eevNm6p34J7gxUQRiqP0VdKhebh6AYvv6EwUNCWmOgS9IwqXzkpDMZwrXPQRwM0NLwoAaPyjC10YKTJlGdmqWyc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=GzparWO/lEN2Vm9KXsqiWtDS60Olg+mi2iBxg3CFwtnGtRdl6af2ICSjiHDaxec1FOY1MkWVGwUskkTnTw0QV6j1oFZ/lLhBzmIWTfeOEmH1FF5Gwnmlu68eVTxaB4R7UdtKE2QZByXqFlEylUAiNdNHoNwG25c3UvFQUJBFjks= Received: by 10.100.93.5 with SMTP id q5mr3270428anb.1181054803897; Tue, 05 Jun 2007 07:46:43 -0700 (PDT) Received: by 10.100.42.1 with HTTP; Tue, 5 Jun 2007 07:46:43 -0700 (PDT) Message-ID: <1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com> Date: Tue, 5 Jun 2007 17:46:43 +0300 From: "Loredana Curugiu" To: "Oliveiros Cristina" , pgsql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] JOIN In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13325_6567236.1181054803839" References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com> <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/35 X-Sequence-Number: 18933 ------=_Part_13325_6567236.1181054803839 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline > > Hmm...try to add the following clause to your INNER JOIN > AND A.date = B.Date > > Like this : > > INNER JOIN view_sent_messages B > ON A.theme=B.theme > AND A.receiver=B.receiver > AND A.date = b.Date > AND B.date=ANY (A.dates) Doesn't work. I get the result sum | theme | receiver | dates -----+--------+--------------+-------------------------------------------------------------------------------- 3 | CRIS | +40741775622 | {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,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 1 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 9 | CRIS | +40741775622 | {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,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 2 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 2 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} Which is not correct. The wrong values ( red colored ) remain as before adding the clause. And now it is summed the counter's values per day ( first day of dates array ). I have not your data here, so I am not sure if it'll work. > Also, Ive never worked with vectors on Postgres. I am assuming ANY() > returns true if B.date is on the vector A.dates, is this correct?? Correct. Regards, Loredana ------=_Part_13325_6567236.1181054803839 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date

Like this :

INNER JOIN view_sent_messages B
              ON A.theme=B.theme
            AND A.receiver=B.receiver
             AND A.date = b.Date
             AND B.date=ANY (A.dates)

Doesn't work. I get the result

 sum | theme  |   receiver   |                                     dates
-----+--------+--------------+--------------------------------------------------------------------------------
   3 | CRIS | +40741775622 | {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,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
   1 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
   9 | CRIS | +40741775622 | {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,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
   2 | LIA    | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
   2 | LIA    | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
   4 | LIA    | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}

 Which is not correct. The wrong values (  red colored ) remain as before
 adding the clause. And now it is summed  the counter's values per day
 ( first day of dates array ).

I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns true if B.date is on the vector A.dates, is this correct??
Correct.
 
Regards,
        Loredana
------=_Part_13325_6567236.1181054803839--