sql notes: leetcode#584 find customer referee

Problem

Given a table customer holding customers information and the referee.

1
2
3
4
5
6
7
8
9
10

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+

Write a query to return the list of customers NOT referred by the person with id ‘2’.

For the sample data above, the result is:

1
2
3
4
5
6
7
8

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

Analysis

• Return list of customer names:

1

SELECT name

• Table customer:

1

SELECT name FROM customer

• Not referred by person with id ‘2’ ==> referee_id can be NULL or any value other than 2:

1

SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;

Solution

1

SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;

Link

584. Find Customer Referee
(中文版) SQL 笔记: Leetcode#584 Find Customer Referee