wiLLiamcastrO wiLLiamcastrO - 29 days ago 7
MySQL Question

MySQL get records beetween tables with conditions

I've got a big problem in my hands, I have the following SQL structure, where the contracts tables are dinamically generated, with random names, like _xyz, _xxx, etc:

CREATE TABLE contract_xyz(
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL
);
CREATE TABLE contract_events(
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_contract INT(11) NOT NULL,
table_contract VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
INSERT INTO contract_xyz (id,created_at) VALUES (1,'2016-11-01');
INSERT INTO contract_xyz (id,created_at) VALUES (2,'2016-10-21');
INSERT INTO contract_xyz (id,created_at) VALUES (3,'2016-11-04');
INSERT INTO contract_events(id,id_contract,table_contract,created_at) VALUES (1,1,'contract_xyz','2016-11-03');
INSERT INTO contract_events(id,id_contract,table_contract,created_at) VALUES (2,3,'contract_xyz','2016-11-04');


Each contract can have his own events. I need to solve the following issue:

Get all contracts that don't have new events in 2 days, or don't have any event at all, and was created over 2 days ago.

I've tried with LET JOIN but it wasn't the correct result. The nearest I get was the following query:

SELECT `contract_xyz`.*
FROM `contract_xyz`
WHERE EXISTS(SELECT 1
FROM `contract_events`
WHERE
`contract_events`.id_contract = `contract_xyz`.id AND `contract_events`.table_contract = 'contract_xyz'
AND DATEDIFF(CURDATE(), `contract_events`.created_at) >= 2
ORDER BY `contract_events`.created_at DESC
LIMIT 1)
OR (NOT EXISTS(SELECT 1
FROM `contract_events`
WHERE `contract_events`.id_contract = `contract_xyz`.id AND
`contract_events`.table_contract = 'contract_xyz') AND
DATEDIFF(CURDATE(), `contract_xyz`.created_at) >= 2);


But I still can't find the contracts that doesn't have any events, and was created over 2 days ago.

Answer

I would create a subquery with the max event date for each contract. I would left join the contracts table on this subquery. You can filter based on the max event date and the created date fields to achieve the expected outcome:

select c.*
from contract_xyz c
left join 
    (select id_contract,
           max(created_at) max_event_date
     from contract_events
     group by id_contract) t on c.id-t.id_contract
 where
     DATEDIFF(CURDATE(), t.max_event_date) >= 2
     or (t.max_event_date is null and DATEDIFF(CURDATE(), c.created_at) >= 2)

Alternatively, you do not use a subquery, but join the 2 tables directly with group by and do the filtering in the having clause.

Comments