Employees Without a Reachable Emergency Contact

Easy
225.

Employees Without a Reachable Emergency Contact

Before the annual safety audit, HR checks that every current employee has someone to call in an emergency. A contact only counts if it has a phone number — rows where phone is NULL were saved half-filled from the onboarding form.

Return the emp_id and full_name of every employee whose status is not 'exited' and who has no emergency contact with a non-NULL phone. Exited employees are never in the answer. Return the rows in any order.

Tables

Table: Employee

ColumnTypeValues
emp_idint
full_namevarchar
statusenumactive, notice, exited

Primary key: emp_id.

One row per employee, past or present.

Table: EmergencyContact

ColumnType
contact_idint
emp_idint
contact_namevarchar
relationvarchar
phonebigint

Primary key: contact_id.

An employee may list several contacts. phone is a 10-digit mobile number, or NULL when it was never filled in.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Employee

emp_idfull_namestatus
201Ananya Reddyactive
202Ishaan Guptaactive
203Pooja Mehtanotice
204Farhan Khanexited
205Tanvi Boseactive

EmergencyContact

contact_idemp_idcontact_namerelationphone
1201Suresh ReddyFather9845012345
2202Lata GuptaMotherNULL
3203Amit MehtaSpouse9123456780
4203Kiran MehtaSiblingNULL
5205Nisha BoseSpouseNULL
6205Raj BoseFatherNULL

Output

emp_idfull_name
202Ishaan Gupta
205Tanvi Bose
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Employees Without a Reachable Emergency Contact

Before the annual safety audit, HR checks that every current employee has someone to call in an emergency.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #225

Problem statement

Before the annual safety audit, HR checks that every current employee has someone to call in an emergency. A contact only counts if it has a phone number — rows where phone is NULL were saved half-filled from the onboarding form.

Return the emp_id and full_name of every employee whose status is not 'exited' and who has no emergency contact with a non-NULL phone. Exited employees are never in the answer. Return the rows in any order.

Tables

Table: Employee

ColumnType
emp_idint
full_namevarchar
statusenum(active, notice, exited)

Primary key: emp_id.

One row per employee, past or present.

Table: EmergencyContact

ColumnType
contact_idint
emp_idint
contact_namevarchar
relationvarchar
phonebigint

Primary key: contact_id.

An employee may list several contacts. phone is a 10-digit mobile number, or NULL when it was never filled in.

Examples

Example 1

Employee

emp_idfull_namestatus
201Ananya Reddyactive
202Ishaan Guptaactive
203Pooja Mehtanotice
204Farhan Khanexited
205Tanvi Boseactive

EmergencyContact

contact_idemp_idcontact_namerelationphone
1201Suresh ReddyFather9845012345
2202Lata GuptaMotherNULL
3203Amit MehtaSpouse9123456780
4203Kiran MehtaSiblingNULL
5205Nisha BoseSpouseNULL
6205Raj BoseFatherNULL

Output

emp_idfull_name
202Ishaan Gupta
205Tanvi Bose

How to solve Employees Without a Reachable Emergency Contact

We want employees for whom no usable contact exists — an anti join, with a twist: the partner rows must themselves pass a filter (phone IS NOT NULL).

With a LEFT JOIN the twist decides where the condition goes. Written in the ON clause, c.phone IS NOT NULL limits which contacts may match; an employee whose contacts all lack a phone then matches nothing and comes out once with NULLs, which c.contact_id IS NULL keeps. Written in WHERE instead, it would run after the join and also throw away the very NULL-extended rows we are looking for, so the query would return nobody.

NOT EXISTS with the phone test inside the subquery reads the same rule directly. NOT IN is safe here only because the subquery selects emp_id, which is never NULL on a contact; were it nullable, one NULL would make every NOT IN unknown. Exited people are filtered on the employee side in all three. Each form is one lookup per employee with an index on EmergencyContact.emp_id.

Reference solution (MySQL)

SELECT e.emp_id, e.full_name
FROM Employee e
LEFT JOIN EmergencyContact c ON c.emp_id = e.emp_id AND c.phone IS NOT NULL
WHERE e.status <> 'exited' AND c.contact_id IS NULL

Another way

SELECT emp_id, full_name FROM Employee e WHERE status <> 'exited' AND NOT EXISTS (SELECT 1 FROM EmergencyContact c WHERE c.emp_id = e.emp_id AND c.phone IS NOT NULL)

Another way

SELECT emp_id, full_name FROM Employee WHERE status IN ('active', 'notice') AND emp_id NOT IN (SELECT emp_id FROM EmergencyContact WHERE phone IS NOT NULL)

← On-Payroll Headcount by Department and Work Mode · Work E-mail Addresses for New Joiners →