From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: Oliveiros Cristina <oliveiros.cristina@gmail.com>
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
Date: Tue, 5 Jun 2007 17:46:43 +0300
Message-ID: <1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com> (raw)
In-Reply-To: <f54607780706050730i23979220t97bdef2e199c09d6@mail.gmail.com>
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
<f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
<1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
<1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com>
<f54607780706050730i23979220t97bdef2e199c09d6@mail.gmail.com>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: loredana.curugiu@gmail.com, oliveiros.cristina@gmail.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox