Home > mailing lists
Re: JOIN - Mailing list pgsql-sql

From	Richard Huxton
Subject	Re: JOIN
Date	June 5, 2007 13:28:50
Msg-id	46656368.7080504@archonet.com Whole thread Raw
In response to	Re: JOIN ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
List	pgsql-sql
Tree view
Loredana Curugiu wrote:
>>> You don't actually say what's wrong. What are you expecting as output?
>
> I should obtain the following result:
[snip]

Well, I've attached a test script using your example data and a copy of
my results. Nothing leaping out as wrong here. It's entirely possible
I've not had enough coffee today though and I'm missing something
staring me in the face...

--
   Richard Huxton
   Archonet Ltd
 sum | theme |   receiver   |          date          |                                     dates
              

-----+-------+--------------+------------------------+--------------------------------------------------------------------------------
   3 | CRIS  | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
   2 | CRIS  | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
   3 | CRIS  | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
  18 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{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 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
   4 | LIA   | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
   6 | LIA   | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
  10 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)

 count | theme |   receiver   |          date          |                                     dates
                

-------+-------+--------------+------------------------+--------------------------------------------------------------------------------
     3 | CRIS  | +40741775622 | 2007-06-01 00:00:00+00 |
{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 00:00:00+00 |
{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-02 00:00:00+00 |
{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-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | CRIS  | +40741775622 | 2007-06-03 00:00:00+00 |
{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-03 00:00:00+00 |
{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-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{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-04 00:00:00+00 |
{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-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{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-04 00:00:00+00 |
{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-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{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-04 00:00:00+00 |
{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-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | CRIS  | +40741775622 | 2007-06-04 00:00:00+00 |
{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 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     2 | LIA   | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     2 | LIA   | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
     2 | LIA   | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
     2 | LIA   | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
     2 | LIA   | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     1 | LIA   | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
(40 rows)

/*
CREATE TABLE loredana_test (
    count    int4,
    theme    text,
    receiver    text,
    "date"    timestamptz,
    dates    date[]
);

COPY loredana_test FROM stdin WITH DELIMITER '|';
2|LIA|+40741775621|2007-06-02 00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
3|CRIS|+40741775622|2007-06-01
00:00:00+00|{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-04
00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
2|LIA|+40741775621|2007-06-03 00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1|CRIS|+40741775622|2007-06-04
00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1|CRIS|+40741775622|2007-06-03
00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1|CRIS|+40741775622|2007-06-04
00:00:00+00|{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 00:00:00+00|{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1|CRIS|+40741775622|2007-06-02
00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
\.
*/

-- Uncomment one of these to control whether any rows match
SET timezone = 'GMT';
-- SET timezone = 'GB';


-- Original query, but with the "date" column displayed too
SELECT
    SUM(A.count),
    A.theme,
    A.receiver,
    A.date,
    A.dates
FROM
    loredana_test A
INNER JOIN
    loredana_test B
ON
    A.theme=B.theme
    AND A.receiver=B.receiver
    AND A.date = ANY(B.dates)
GROUP BY
    A.theme,A.receiver,A.date,A.dates;


-- Query to test matches
SELECT
    A.count,
    A.theme,
    A.receiver,
    A.date,
    B.dates
FROM
    loredana_test A
INNER JOIN
    loredana_test B
ON
    A.theme=B.theme
    AND A.receiver=B.receiver
    AND A.date = ANY(B.dates)
ORDER BY
    A.theme, A.receiver, A.date
;
pgsql-sql by date:
From: "Oliveiros Cristina"
Date: 05 June 2007, 13:26:59
Subject: Re: JOIN
From: Sean Davis
Date: 05 June 2007, 13:44:27
Subject: Re: [NOVICE] JOIN
Re: JOIN - Mailing list pgsql-sql

Previous

Next
