Need to pull by date, that a value appears from different tables

Question

I have tried unions, joins, distinct and minimum functions with some success, but still slightly off. Example of the tables are below

ID      date
1            01-02-2020
2            02-02-2020
3            04-06-2020
4            01-03-2019

ID      date
1            01-03-2018
2            07-02-2019
3            10-02-2020
4            09-02-2020

What I want is

ID  date
1        01-02-2020
2        01-03-2019
3        01-03-2018
4        07-02-2019
5        10-02-2020

nbk · Accepted Answer · 2021-07-11T00:49:20.793

It is not pretty

But a Full OUT JOIN which MySQL doesn't have, of both tables did the trick

CREATE TABLE tab1 (
  `ID` INTEGER,
  `Value_ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO tab1
  (`ID`, `Value_ID`, `date`)
VALUES
  ('1', '1', '01-02-2020'),
  ('2', '2', '02-02-2020'),
  ('3', '3', '04-06-2020'),
  ('4', '2', '01-03-2019');

CREATE TABLE tab2 (
  `ID` INTEGER,
  `Value_ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO tab2
  (`ID`, `Value_ID`, `date`)
VALUES
  ('1', '3', '01-03-2018'),
  ('2', '4', '07-02-2019'),
  ('3', '5', '10-02-2020'),
  ('4', '4', '09-02-2020');

SELECT COALESCE(t2.`Value_ID`,t1.`Value_ID`) as `Value_ID`,IF (IFNULL(t1.`date`,'31-12-9999') <= t2.`date`,t1.`date`,t2.`date`)
FROM (SELECT `Value_ID`, MIN(`date`) as `date` FROM tab1 GROUP BY `Value_ID`) t1 
right JOIN (SELECT `Value_ID`, MIN(`date`) as `date` FROM tab2 GROUP BY `Value_ID`) t2 
ON t1.`Value_ID` = t2.`Value_ID`
UNION
SELECT COALESCE(t1.`Value_ID`,t2.`Value_ID`),IF (t1.`date` <= IFNULL(t2.`date`,'31-12-9999'),t1.`date`,t2.`date`)
FROM (SELECT `Value_ID`, MIN(`date`) as `date` FROM tab1 GROUP BY `Value_ID`) t1 
LEFT JOIN (SELECT `Value_ID`, MIN(`date`) as `date` FROM tab2 GROUP BY `Value_ID`) t2 
ON t1.`Value_ID` = t2.`Value_ID`

ORDER BY `Value_ID`

Value_ID | IF (IFNULL(t1.`date`,'31-12-9999') <= t2.`date`,t1.`date`,t2.`date`)
-------: | :-------------------------------------------------------------------
       1 | 01-02-2020                                                          
       2 | 01-03-2019                                                          
       3 | 01-03-2018                                                          
       4 | 07-02-2019                                                          
       5 | 10-02-2020

db<>fiddle here

You can use Window functions

Schema (MySQL v8.0)

CREATE TABLE tab1 (
  `ID` INTEGER,
  `Value_ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO tab1
  (`ID`, `Value_ID`, `date`)
VALUES
  ('1', '1', '01-02-2020'),
  ('2', '2', '02-02-2020'),
  ('3', '3', '04-06-2020'),
  ('4', '2', '01-03-2019');
  CREATE TABLE tab2 (
  `ID` INTEGER,
  `Value_ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO tab2
  (`ID`, `Value_ID`, `date`)
VALUES
  ('1', '3', '01-03-2018'),
  ('2', '4', '07-02-2019'),
  ('3', '5', '10-02-2020'),
  ('4', '4', '09-02-2020');

Query #1

SELECT 
    `Value_ID`, `date`
FROM
(SELECT
`Value_ID`, `date`
, ROW_NUMBER() OVER (PARTITION BY `Value_ID` ORDER BY `date` ASC) rn
FROM
(SELECT `Value_ID`, MIN(`date`) as `date` FROM tab1 GROUP BY `Value_ID`
UNION
SELECT `Value_ID`, MIN(`date`) as `date` FROM tab2 GROUP BY `Value_ID`) t1
 ) t3
 WHERE rn = 1;

Value_ID	date
1	01-02-2020
2	01-03-2019
3	01-03-2018
4	07-02-2019
5	10-02-2020

View on DB Fiddle

I apologize if this is rude to ask but is there any simpler way to do that? I only ask because this answer is going to be reviewed by a peer and if I sent them this it would be fairly obvious it was not my work. But again I appreciate your help! — NewGB, Jul 11 '21 at 00:33

Need to pull by date, that a value appears from different tables

1 Answers1