Home > mailing lists

Help with a JOIN. - Mailing list pgsql-novice

From	Paul Linehan
Subject	Help with a JOIN.
Date	July 20, 2014 15:13:01
Msg-id	CAF4RT5RhceTXRx-ox4dVwZzM=WeXCzTZYzoKekV8OctSk46cNQ@mail.gmail.com Whole thread Raw
Responses	Re: Help with a JOIN.
List	pgsql-novice

Tree view

Hi all,

I've got a problem with a query I'm trying. I've got it
working with a CTE, but I'd like to do it as a JOIN.

I've given the DDL and DML at the end of this post.

I have two tables: tickets and comments. I need to run a
report that shows me which ticket is "neglected" meaning
that there has not been a comment in X amount of time or

not been modified for X amount of time. The important field
here is comments_timestamp.

First, I'll show the CTE and the result (correct) that I want.

WITH t2 AS
(
SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date
FROM comment c
WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
GROUP BY ticket_id
)
SELECT t2.ticket_id, t.ticket_description, t2.c_max_date,
t2.c_max_date AS cte_c_max_date, c.comment_id, c.comments_comment,
c.comments_timestamp AS com_c_max_date
FROM ticket t, t2, comment c
WHERE t.ticket_id = t2.ticket_id
AND t2.c_max_date = c.comments_timestamp;

ticket_id | ticket_description | c_max_date | cte_c_max_date | comment_id |   comments_comment    | com_c_max_date
-----------+--------------------+------------+----------------+------------+-----------------------+----------------
         3 | ticket 3           | 1171379773 |     1171379773 |         10 | comment 2 on ticket 3 |     1171379773
         4 | ticket 4           | 1167484540 |     1167484540 |         15 | comment 3 on ticket 4 |     1167484540

Now, I'm trying to run a JOIN like this

SELECT t.ticket_id, t.ticket_description, x.c_max_date, x.c_comment
FROM
(
SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date, MAX(comments_comment) AS c_comment
FROM comment c
WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
GROUP BY ticket_id
)
AS x INNER JOIN ticket AS t ON t.ticket_id = x.ticket_id;

The problem is that the comment is not correct for the given ticket date - it's
picking up what appears to be the last comment alphabetically for the given

ticket_id - I want the comment (obviously) associated with the date (which
is the latest comments_timestamp for the given ticket_id where the ticket
hasn't been modified for 60000000 seconds (approx. 2 years).

Any help in getting this query to work as a JOIN would be appreciated.

I know that the date stuff is a bit wierd - I'll fix that later. Finally, for a
bonus, I would also have to do this for MySQL if anyone also has

any clues about how to do it for that server, that would be great.

============ DDL and DML for tables ============

CREATE TABLE IF NOT EXISTS ticket
(
ticket_id INT NOT NULL,
ticket_description VARCHAR(25),
PRIMARY KEY (ticket_id)
);

CREATE TABLE IF NOT EXISTS comment
(
comment_id INT NOT NULL,
ticket_id INT NOT NULL,
comments_comment VARCHAR(45) NULL,
comments_timestamp INT NULL,
PRIMARY KEY (comment_id)
);

INSERT INTO ticket VALUES(1, 'ticket 1');
INSERT INTO ticket VALUES(2, 'ticket 2');
INSERT INTO ticket VALUES(3, 'ticket 3');
INSERT INTO ticket VALUES(4, 'ticket 4');
INSERT INTO ticket VALUES(5, 'ticket 5');

INSERT INTO comment VALUES(1, 1, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:31'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:34'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:36'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:37'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:35'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:34'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:38'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:01'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:12'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2007-02-13 15:16:13'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2003-06-30 15:17:23'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:12'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-10-30 15:18:07'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 14:15:04'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2006-12-30 13:15:40'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2004-12-09 12:15:31'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:32'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:33'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:34'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:35'));

======== DDL and DML for the tables ==========

TIA and rgs,

Paul...

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

pgsql-novice by date:

From: Gerald Cheves
Date: 20 July 2014, 06:09:57
Subject: Re: JBDC LDAP support

From: spake@surewest.net
Date: 20 July 2014, 15:13:47
Subject: Re: PGSQL 9.3.2 COPY command issues

Help with a JOIN. - Mailing list pgsql-novice

Previous

Next