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

Re: [SQL] JOIN

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>,"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 15:00:27
Message-ID: 012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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
   
  From: Loredana Curugiu 
  To: Oliveiros Cristina ; pgsql-novice(at)postgresql(dot)org ; pgsql-sql(at)postgresql(dot)org 
  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

In response to

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2007-06-05 15:10:51
Subject: Re: Assigning Password to New created user
Previous:From: Loredana CurugiuDate: 2007-06-05 14:57:13
Subject: Re: [SQL] JOIN

pgsql-sql by date

Next:From: Marko KreenDate: 2007-06-05 15:09:43
Subject: Re: Encrypted column
Previous:From: Loredana CurugiuDate: 2007-06-05 14:57:13
Subject: Re: [SQL] JOIN

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