SQL filter table based on two cell values-CodePudding

I have a table with contact details “lead” and another table with information about emails which have been sent to the leads “mail_log”

I want to get all leads which have received mail_id = 1 but not mail_id = 2. And the lead should not be deleted = 1. And the timestamp of mail_id 1 sould be more then one week ago

As result I need the email address of the lead and the date when the last mail was sent (in this case mail_id 1)

Thank you

CREATE TABLE `lead` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `data_id` INT(11) NULL DEFAULT NULL,
    `email` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_german2_ci',
    `deleted` TINYINT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)COLLATE='latin1_german2_ci' ENGINE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (1, 217, '[email protected]', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (2, 578, '[email protected]', NULL);
INSERT INTO `lead` (`id`, `data_id`, `email`, `deleted`) VALUES (3, 124, '[email protected]', NULL);



CREATE TABLE `mail_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `lead_id` INT(11) NULL DEFAULT NULL,
    `mail_id` INT(11) NULL DEFAULT NULL,
    `timestamp` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)COLLATE='latin1_german2_ci' ENGINE=MyISAM ROW_FORMAT=FIXED AUTO_INCREMENT=4 ;

INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (1, 217, 1, '2022-03-03 00:00:00');
INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (2, 578, 1, '2022-03-03 00:00:00');
INSERT INTO `mail_log` (`id`, `lead_id`, `mail_id`, `timestamp`) VALUES (3, 217, 2, '2022-03-14 00:00:00');

CodePudding user response：

select l.email,
       ml.timestamp
from lead l
join mail_log ml on l.id = ml.lead_id
where l.deleted <> 1
    and ml.mail_id = 1
    and ml.timestamp < DATEADD(day, -7, CAST(GETDATE() AS date))

CodePudding user response：

Since you only want data from Lead you can utilise exists here:

select * 
from `lead` l
where deleted is null
  and exists (
    select * from mail_log ml 
    where ml.lead_id = l.data_id and ml.mail_id = 1 and ml.timestamp < date_add(curdate(), interval -1 week) 
  )
  and not exists (
    select * from mail_log ml 
    where ml.lead_id = l.data_id and mail_id = 2
  );

DB<>Fiddle