Skip site navigation (1) Skip section navigation (2)

Re: [SQL] JOIN

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: "Oliveiros Cristina" <oliveiros(dot)cristina(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] JOIN
Date: 2007-06-05 14:46:43
Message-ID: 1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
>
> 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

In response to

Responses

pgsql-novice by date

Next:From: Oliveiros CristinaDate: 2007-06-05 14:52:49
Subject: Re: [SQL] JOIN
Previous:From: Oliveiros CristinaDate: 2007-06-05 14:30:41
Subject: Re: [SQL] JOIN

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2007-06-05 14:52:49
Subject: Re: [SQL] JOIN
Previous:From: Michael GlaesemannDate: 2007-06-05 14:40:50
Subject: Re: CREATE RULE with WHERE clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group