By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE LayoutVariables (
alv_id INT,alv_advertiser_id INT,alv_layout_variable_key VARCHAR(200),
alv_layout_variable_value VARCHAR(200),alv_last_modified_on INT,alv_last_modified_by INT);
INSERT INTO LayoutVariables VALUES
(11,8167,'var4','var val 40',1624550014,2025),
(12,7690,'amazon_usa','amazon_usa_vairaible',1624589928,2021);
CREATE TABLE LayoutMapping (
alm_id INT,alm_advertiser_id INT,alm_layout_id INT,
alm_last_modified_on INT,alm_last_modified_by INT);
INSERT INTO LayoutMapping VALUES
(5 ,8167 ,334455 ,1624436972 ,2025),
(12 ,7690 ,123 ,1624320000 ,2021);
CREATE TABLE Advertiser (
av_id INT,av_advertiser_name VARCHAR(200),av_is_active INT);
INSERT INTO Advertiser VALUES
(8167,'TestAdvertiser',1),
(7690,'amazon_usa',1);
SELECT
ADV.av_id,
ADV.av_advertiser_name,
ADV.av_is_active,
AVLV.alv_advertiser_id ,
AVLM.alm_advertiser_id ,
IF(AVLV.alv_advertiser_id IS NOT NULL, TRUE, FALSE) AS isBrandConfigDone,
IF(AVLM.alm_advertiser_id IS NOT NULL, TRUE, FALSE) AS isLayoutMappingDone,
mm
FROM Advertiser AS ADV
LEFT JOIN LayoutVariables AS AVLV ON ADV.av_id = AVLV.alv_advertiser_id
LEFT JOIN LayoutMapping AS AVLM ON ADV.av_id = AVLM.alm_advertiser_id
LEFT JOIN (SELECT MAX(lmd) AS mm FROM
(SELECT alm_last_modified_on lmd FROM LayoutMapping UNION
SELECT alv_last_modified_on FROM LayoutVariables ) v) mx
ON AVLV.alv_last_modified_on=mm
| av_id | av_advertiser_name | av_is_active | alv_advertiser_id | alm_advertiser_id | isBrandConfigDone | isLayoutMappingDone | mm |
|---|---|---|---|---|---|---|---|
| 8167 | TestAdvertiser | 1 | 8167 | 8167 | 1 | 1 | null |
| 7690 | amazon_usa | 1 | 7690 | 7690 | 1 | 1 | 1624589928 |