Re: Help with a JOIN. - Mailing list pgsql-novice

>>> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Paul Linehan

>>> Sent: Sunday, July 20, 2014 3:04 AM

>>> To: Gerald Cheves

>>> Cc: pgsql-novice@postgresql.org

>>> Subject: [NOVICE] 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

>>>

[[KenB]]  You're asking for the MAX(comments_comment) … which is what you're getting.

What you actually want is the comment that comes from the row that has the MAX(comments_timestamp)

I think you need an additional join statement:             

SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.coments_comment

FROM                                                       

 (                                                          

   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                                       

 )                                                           

 AS x INNER JOIN ticket AS t ON t.ticket_id = x.ticket_id   

 INNER JOIN comment AS z ON z.comments_timestamp = x.c_max_date;

>>> 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