What subquery do I need to calculate this report? 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. 

If I was to do this in PHP, I would load ticket, then do a separate query for each one, see what the most recent timestamp was (max(comment_ts)), and keep the values of the "old tickets" in an array, then display them from there. But, it seems I should be able to do this as a single query via MySQL and it would be much faster, so I am trying to create a view for this.

Assume this is the basic table structure:

-- MySQL Script generated by MySQL Workbench
-- 07/18/14 17:29:39
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`tickets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`tickets` (
  `ticket_id` INT NOT NULL,
  `ticket_description` TEXT NULL,
  PRIMARY KEY (`ticket_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`comments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
  `comments_id` INT NOT NULL,
  `comments_comment` VARCHAR(45) NULL,
  `comments_timestamp` INT NULL,
  PRIMARY KEY (`comments_id`),
  CONSTRAINT `comments_bug`
    FOREIGN KEY (`comments_id`)
    REFERENCES `mydb`.`tickets` (`ticket_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
I've got this far (DDL and DML below)

SELECT ticket_id, c_max_date FROM
( 
  SELECT ticket_id, MAX(FROM_UNIXTIME(comment.comments_timestamp)) AS c_max_date
  FROM comment
  WHERE comment.comments_timestamp &lt; UNIX_TIMESTAMP(NOW() - INTERVAL 2 YEAR)
  GROUP BY ticket_id
) AS mytab;

+-----------+---------------------+
| ticket_id | c_max_date          |
+-----------+---------------------+
|         3 | 2004-07-09 15:15:30 |
|         4 | 2006-06-30 15:15:30 |
+-----------+---------------------+


These two comments are indeed the ticket number of tickets whose comments haven't been updated in 2 years (arbitrary choice). The dates are the dates of the last update to the ticket comments.

Can you (anyone) put the finishing touch to this and join mytab to tickets? My brain is fried!

CREATE TABLE IF NOT EXISTS ticket
(
  ticket_id INT NOT NULL,
  ticket_description TEXT NULL,
  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),
  INDEX ticket_index (ticket_id),
  FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_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', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', UNIX_TIMESTAMP('2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', UNIX_TIMESTAMP('2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', UNIX_TIMESTAMP('2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', UNIX_TIMESTAMP('2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', UNIX_TIMESTAMP('2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', UNIX_TIMESTAMP('2006-06-30 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2014-07-09 15:15:30'));