Question

584. Find Customer Referee

中文文档

Description

Table: Customer

+-------------+---------+
| Column Name | Type  |
+-------------+---------+
| id      | int   |
| name    | varchar |
| referee_id  | int   |
+-------------+---------+
In SQL, id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

 

Find the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null     |
| 2  | Jane | null     |
| 3  | Alex | 2      |
| 4  | Bill | null     |
| 5  | Zack | 1      |
| 6  | Mark | 2      |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

Solutions

Solution 1: Conditional Filtering

We can directly filter out the customer names whose referee_id is not 2. Note that the customers whose referee_id is NULL should also be filtered out.

# Write your MySQL query statement below
SELECT name
FROM Customer
WHERE IFNULL(referee_id, 0) != 2;