CODEWITHSUNDEEP
mysqlsqlsubquery
ratherBeKiting
ratherBeKiting

Reputation: 275

Getting records with no matching records in joined table

I have the below 2 tables MySQL database.

CREATE TABLE `leads` (
  `lead_id` int(11) NOT NULL,
  `fname` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `lname` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `phone_mobile` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `leads`
  ADD PRIMARY KEY (`lead_id`);

ALTER TABLE `leads`
  MODIFY `lead_id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `card_results` (
  `card_result_id` int(11) NOT NULL,
  `lead_id` int(11) NOT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `card_results`
  ADD PRIMARY KEY (`card_result_id`),
  ADD KEY `created` (`created`),
  ADD KEY `success` (`success`);

ALTER TABLE `card_results`
  MODIFY `card_result_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `card_results`
  ADD CONSTRAINT `FK_card_results_lead_id` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`lead_id`) ON DELETE CASCADE ON UPDATE CASCADE,
COMMIT;

I wish to find all records in the leads table where there are no matching records in the card_results table in the last 3 months.

The 2 tables are related by the foreign key 'lead_id'

My query is below:

SELECT l.lead_id
FROM leads l
WHERE 
    (SELECT COUNT(*) 
     FROM card_results c
     WHERE success = 1
       AND created >= NOW()-INTERVAL 3 MONTH
       AND c.lead_id = l.lead_id) = 0

The query runs a little slow and I would like to optimise it.

Is there another way to achieve the same result.

Upvotes: 0

Views: 56

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY in one of the following ways:

Using NOT IN 

SELECT l.lead_id
FROM leads l
WHERE l.lead_id NOT IN (
    SELECT c.lead_id 
    FROM card_results c
    WHERE success = 1
    AND created >= NOW()-INTERVAL 3 MONTH)

Using JOIN and IS NULL

SELECT l.lead_id
FROM leads l
LEFT JOIN card_results c ON c.lead_id = l.lead_id
    AND c.success = 1
    AND created >= NOW()-INTERVAL 3 MONTH
WHERE c.lead_id IS NULL

Using NOT EXISTS

SELECT l.lead_id
FROM leads l
WHERE NOT EXISTS 
    (SELECT 1
     FROM card_results c
     WHERE success = 1
       AND created >= NOW()-INTERVAL 3 MONTH
       AND c.lead_id = l.lead_id)

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

You could use LEFT join with IS NULL make sure your filters should be used in ON clause rather than WHERE

SELECT l.lead_id
FROM leads l
LEFT JOIN card_results c 
       ON l.lead_id = c.lead_id
       AND c.created >= NOW()-INTERVAL 3 MONTH
       AND c.success = 1
WHERE c.lead_id IS NULL

Upvotes: 2

Related Questions