Mysql selecting greatest date from two tables joined by lookup table

[TheBigJohn]

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

[FatmaNagori]

I can’t understand why are you creating lookup_table1_table2 table.
if you only want to find the largest date from two tables you can use following query :

SELECT MAX(a.date_modified) FROM( 
      SELECT date_modified FROM table1
      UNION ALL
      SELECT MAX(date_modified) FROM table2) a;

[TheBigJohn]

Hi Fatma,

Hi, it’s not that I want the largest date from the tables, I want the largest date for each of the IDs in table 1.

So results should be the ID number from table1, along with the greatest date from either table 1 (if that’s greater than the dates joined in table 2) or the greatest date in table two for the ID associated through the lookup table. Whichever is the greater.

Sorry, I’ve maybe not explained that so well. The reason there’s a lookup table is that data in table two is also joined to other tables through similar lookups. It’s so data isn’t duplicated.

[FatmaNagori]

i couldn’t understand full thing what you want but i have a query you can try and i just hope it is that you want .

SELECT a.table1_id,Max(a.date_modified) FROM (
    SELECT table1.table1_id,table1.date_modified FROM lookup 
    INNER JOIN  table1 ON lookup.table1_id=table1.table1_id 
    UNION ALL 
    SELECT table2.table2_id,table2.date_modified FROM lookup 
     INNER JOIN table2 ON lookup.table2_id=table2.table2_id
)a;