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:
| 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 |
Solution :
select name from customer where referee_id != 2 or referee_id is null;Explanation –
SELECT name: This part selects the name column from the table customer and retrieves the names of customers based on the given conditions.
WHERE referee_id !=2 OR referee_id IS NULL: The WHERE clause filters the results based on two conditions connected by the OR operator:
a. referee_id != 2: This condition excludes rows where the referee_id is not equal to 2. It filters out customers whose referee_id is not 2.
b. referee_id IS NULL: This condition excludes rows where the referee_id is NULL. It filters out customers who have no referee (referee_id is not specified).
As a result, the query retrieves the names of customers whose referee_id is not equal to 2 or is NULL (customers who either have no referee or have a referee with a value other than 2).
The names “Will,” “Jane,” “Bill,” and “Zack” are returned because they satisfy the specified conditions
Read More –