Help with a JOIN.

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;

But my result are the following

ticket_id | ticket_description | c_max_date | c_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1171379773 | comment 4 on ticket 3
4 | ticket 4 | 1167484540 | comment 4 on ticket 4

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(at)tcd(dot)ie

Mob: 00 353 86 864 5772