Postgres Pro
Downloads
Home   >   mailing lists

Re: JOIN - Mailing list pgsql-sql

From Oliveiros Cristina
Subject Re: JOIN
Date
Msg-id 012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt
Whole thread Raw
In response to Re: JOIN  ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
Responses Re: JOIN
List pgsql-sql
Tree view
And , still, in your query, you are grouping by A.dates... is there any reason for this that I am missing ?
 
     SELECT SUM(A.count),
                     A.theme,
                     A.receiver,
                     A.dates
          FROM my_table A
INNER JOIN my_table B
              ON A.theme=B.theme
            AND A.receiver=B.receiver
            AND A.date=ANY(B.dates)
 GROUP BY A.theme,A.receiver, A.dates;
 
 
If the dates column works as a "discriminator" to see if the row should be considered or not,
maybe this would work
 
SELECT SUM(count), theme,receiver,date
FROM my_table
WHERE date=ANY(dates)
GROUP BY theme,receiver,date ;
 
  But I don't know, do you need to include the column "dates" on output ?
 
Best,
Oliveiros
 
Sent: Tuesday, June 05, 2007 3:46 PM
Subject: Re: [SQL] JOIN

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

pgsql-sql by date:

Previous
From: "Loredana Curugiu"
Date:
Subject: Re: JOIN
Next
From: Gerardo Herzig
Date:
Subject: Re: current_date / datetime stuff