2

I have this schema of dossiers having 1 to N pages, and each page has a date:

CREATE TABLE dossiers 
(id INT AUTO_INCREMENT,
code VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=MyISAM; 

CREATE TABLE pages 
(id INT AUTO_INCREMENT,
name VARCHAR(100),
date DATE,
hour TIME,
dossiers_id INT,
 PRIMARY KEY (id)
) ENGINE=MyISAM; 

INSERT INTO dossiers (id, code)
VALUES (1, 'A1'),
(2, 'A2');

INSERT INTO pages (id, name, date, hour, dossiers_id)
VALUES (1, 'page1', '2018-09-01', '17:00:00', 1),
(2, 'page2', '2018-09-05', '17:00:00', 1),
(3, 'page3', '2018-09-10', '17:00:00', 1),
(4, 'page4', '2018-08-20', '17:00:00', 1),
(5, 'page1', '2018-09-01', '17:00:00', 2);

I want to select all the dossiers alongside the page with its maximum date.

This is what I tried (error):

SELECT *
FROM dossiers d1
INNER JOIN pages ON pages.dossiers_id = d1.id 
WHERE CONCAT(pages.date, ' ', pages.time) = (SELECT MAX(GROUP_CONCAT(date, ' ', time)) FROM pages WHERE dossiers_id = d1.id GROUP BY dossiers_id)

I need to get the A1 dossier along with its "page3" page, and the A2 dossier with its "page1" page.

I'm sure it's not as twisted as I've done, but I can't figure it out.

Suggestions?

Link to the SQL Fiddle here.

1

A solution is to do an extra LEFT OUTER JOIN with 'newer pages', and filtering those with no newer pages:

SELECT d.*, p1.*
  FROM dossiers AS d
  INNER JOIN pages AS p1
    ON d.id = p1.dossiers_id
  LEFT OUTER JOIN pages AS p2
    ON d.id = p2.dossiers_id
   AND (p2.date > p1.date OR (p2.date = p1.date AND p2.hour > p1.hour))
  WHERE p2.id IS NULL

SQLFiddle

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.