CODEWITHSUNDEEP
mysqljoinleft-join
john.p.doe
john.p.doe

Reputation: 521

Left join returning all the results even when the condition is not satisfied

I have a simple query

select * 
from employees as e 
left join email_notification as en 
on e.id=en.eid and en.`status` != 'SUCCESS';

Now I have one record in email_notification table that has status success but still that record is getting fetched from employee table.

Upvotes: 0

Views: 918

Answers (3)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Why don't try using IN?

select * 
from employees as e 
where id not in(
    select eid from email_notification 
    where status != 'SUCCESS'
)

Upvotes: 3

Uueerdo
Uueerdo

Reputation: 15961

You'll want to left join employees to "successes" and choose those who do not have one, like so:

SELECT e.* 
FROM employees AS e 
LEFT email_notification AS en 
ON e.id=en.eid and en.`status` = 'SUCCESS'
WHERE en.id IS NULL
;

Upvotes: 2

skypjack
skypjack

Reputation: 50568

I guess you mean:

select *
from employees as e
left join email_notification as en
on e.id=en.eid
where en.`status` != 'SUCCESS';

I mean, the check on status should not be done along with the join, instead it takes part on the final result.

Upvotes: 1

Related Questions