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.
Link to the SQL Fiddle here.