Understanding the Problem and Requirements

In this article, we will delve into a common problem faced by database administrators and developers alike: retrieving an item from a database that has multiple alternatives. We are given a hypothetical scenario involving three tables - Clients, PhoneType, and Phones. The task is to write a SQL query that returns the cellphone number of a client if it exists, otherwise returns their home number.

Creating the Database Schema

To begin with, let’s create the database schema using the provided SQL scripts:

CREATE TABLE Clients (
    id_client int PRIMARY KEY,
    nm_client varchar(200),
    zipcode int
);

CREATE TABLE PhoneType (
    cd_phoneType int PRIMARY KEY,
    nm_phoneType varchar(60)
);

CREATE TABLE Phones (
    cd_phone int PRIMARY KEY,
    cd_phoneType int,
    nu_phone VARCHAR(20),
    id_client INT,
    FOREIGN KEY (cd_phoneType) REFERENCES PhoneType(cd_phoneType),
    FOREIGN KEY (id_client) REFERENCES Clients(id_client)
);

Understanding the Table Data

Based on the provided data, we have two clients with one phone number each. The Clients table contains the client’s ID, name, and zip code, while the PhoneType table stores the type of phone (landline or cellphone). The Phones table stores the client’s phone numbers along with their corresponding phone types.

Understanding SQL Joining

SQL joining is a crucial concept in database querying. It allows us to combine rows from two or more tables based on a related column between them. In this scenario, we will use INNER JOINs to link the Clients and Phones tables based on the client’s ID, as well as join the resulting table with the PhoneType table.

Solution 1: Using MAX(CASE WHEN…) Syntax

One way to solve this problem is by using the MAX(CASE WHEN…) syntax in SQL. This approach allows us to retrieve multiple columns from a single query while handling NULL values.

SELECT 
  nm_client,
  MAX(CASE WHEN nm_phoneType = 1 THEN nu_phone END) as landline,
  MAX(CASE WHEN nm_phoneType = 2 THEN nu_phone END) as mobile
FROM Clients
JOIN Phones
ON Clients.id_client = Phones.id_client
GROUP BY nm_client;

In this query:

• We join the Clients and Phones tables on the client’s ID.
• We use two CASE statements within the GROUP BY clause to check if the phone type is landline (1) or cellphone (2).
• The MAX function returns the maximum value among these checks for each group of clients.

Solution 2: Using COALESCE Function

Another approach to solve this problem is by using the COALESCE function, which returns the first non-null argument from left to right. This allows us to return multiple values in a single column while ignoring NULL values.

SELECT 
  nm_client,
  COALESCE(
    MAX(CASE WHEN nm_phoneType = 2 THEN nu_phone END),
    MAX(CASE WHEN nm_phoneType = 1 THEN nu_phone END)
  ) as pref_num
FROM Clients
JOIN Phones
ON Clients.id_client = Phones.id_client
GROUP BY nm_client;

In this query:

• We join the Clients and Phones tables on the client’s ID.
• We use two CASE statements within the GROUP BY clause to check if the phone type is landline (1) or cellphone (2).
• The COALESCE function returns the preferred number (cellphone or home) based on the order of arguments.

Solution 3: Using TOP and ORDER BY

If we are only selecting one client at a time, we can use the TOP and ORDER BY clauses to achieve this. This approach is more efficient when dealing with smaller datasets.

SELECT TOP 1 
  nm_client,
  nu_phone
FROM Clients
JOIN Phones
ON Clients.id_client = Phones.id_client
WHERE clients.id_client = 1234
ORDER BY nm_phonetype DESC;

In this query:

• We join the Clients and Phones tables on the client’s ID.
• We use a WHERE clause to filter for a specific client (1234).
• The ORDER BY clause sorts the results in descending order based on phone type, with cellphone numbers appearing first.

Conclusion

In this article, we explored various approaches to solving the problem of retrieving an item from a database that has multiple alternatives. We demonstrated how to use SQL joining, MAX(CASE WHEN…) syntax, COALESCE function, and TOP and ORDER BY clauses to achieve this goal. By understanding these concepts, developers can create efficient and scalable database queries that handle complex data relationships.

Last modified on 2025-04-19