Hello,
This is my first post here. The site looks great and I hope to be able to contribute.
I’m having a bit of trouble with something that seemed relatively trivial to me, but I’m not getting the result set I’m expecting from a variety of ways I’ve tried to query this…
I want to retrieve the greatest date modified from two tables joined by a lookup table. eg
CREATE TABLE lookup_table1_table2
(
lookup_id
int(11) NOT NULL AUTO_INCREMENT,
table1_id
int(11) ,
table2_id
int(11) ,
PRIMARY KEY (lookup_id
));
INSERT INTO lookup_table1_table2
VALUES (‘1’, ‘1’, ‘1’);
INSERT INTO lookup_table1_table2
VALUES (‘2’, ‘1’, ‘2’);
INSERT INTO lookup_table1_table2
VALUES (‘3’, ‘1’, ‘3’);
CREATE TABLE table1
(
table1_id
int(11) NOT NULL AUTO_INCREMENT,
date_modified
datetime,
PRIMARY KEY (table1_id
));
INSERT INTO table1
VALUES (‘1’, ‘2020-01-22 12:15:37’);
INSERT INTO table1
VALUES (‘2’, ‘2020-01-14 12:15:52’);
CREATE TABLE table2
(
table2_id
int(11) NOT NULL AUTO_INCREMENT,
date_modified
datetime,
PRIMARY KEY (table2_id
));
INSERT INTO table2
VALUES (‘1’, ‘2020-01-28 12:16:27’);
INSERT INTO table2
VALUES (‘2’, ‘2020-01-23 12:16:35’);
INSERT INTO table2
VALUES (‘3’, ‘2020-01-31 12:16:42’);
So, expecting ID 1 from table 1 to have the greatest modified datetime of “2020-01-31 12:16:42”. from table 2.
However way I join the tables it seems, I get multiple rows for ID 1.
Help much appreciated.
John