Selecting Friends from Friend Requests Using SQL

Selecting a List of Data Which Can Contain Values from 2 Columns

===========================================================

In this article, we will explore the concept of selecting data from two columns and how to achieve this using SQL. We will use a hypothetical scenario to demonstrate how to retrieve friends of a specific user based on their friend request status.

Understanding Friend Requests

A friend request is a common feature found in many social media platforms and online communities. It allows users to send requests to other users they want to become friends with. The recipient can either accept or reject the request, which determines whether the friendship is established or not.

We will assume that we have a friendrequest table with three columns: id, status, and two foreign keys referencing the user IDs of the sender and recipient.

Creating the Friend Request Table

To create this table, we can use the following SQL code:

CREATE TABLE t (id INTEGER, status TEXT, recipient INTEGER, sender INTEGER);

This creates a table with four columns: id, status, recipient, and sender. The status column stores the current status of the friend request (e.g., “ACCEPTED” or “REJECTED”), while the recipient and sender columns store the IDs of the user who sent the request and received it, respectively.

Inserting Sample Data

To demonstrate our query, we will insert some sample data into the table:

INSERT INTO t
VALUES
(1, 'ACCEPTED', 2, 1),
(2, 'ACCEPTED', 3, 1),
(3, 'ACCEPTED', 1, 4),
(4, 'REJECTED', 5, 1),
(5, 'REJECTED', 1, 5),
(6, 'ACCEPTED', 6, 7),
(7, 'ACCEPTED', 1, 2);

This inserts seven rows into the table, representing friend requests between different users.

Querying Friends

Now that we have our data in place, let’s write a query to retrieve the friends of a specific user. We will use a combination of the IN operator and the CASE statement to achieve this.

The query we will use is:

SELECT DISTINCT 
  CASE sender WHEN 1 THEN recipient ELSE sender END AS friends 
FROM t 
WHERE 1 IN (recipient, sender) 
AND status = 'ACCEPTED' 
ORDER BY 1;

Let’s break down what this query does:

The CASE statement checks whether the sender is equal to 1. If it is, then we return the value of the recipient column; otherwise, we return the value of the sender column.
We filter the results using the WHERE clause. We check that the recipient or sender columns are equal to 1 and that the status is equal to ‘ACCEPTED’. This ensures that only accepted friend requests from users with ID 1 are included in our query results.
Finally, we use the ORDER BY clause to sort the results by the generated column.

Results

When we run this query on the sample data we inserted earlier, we get the following result:

| friends |
| ------- |
| 2       |
| 3       |
| 4       |

This shows us that users with ID 1 have accepted friend requests from users with ID 2, 3, and 4.

Conclusion

In this article, we demonstrated how to select a list of data which can contain values from two columns. We used a hypothetical scenario to illustrate the concept of retrieving friends based on their friend request status.

We discussed the use of SQL queries, including the CASE statement, to achieve this. Our example query retrieves friends of a specific user by filtering accepted friend requests and using a combination of conditional logic and sorting to produce the desired results.

By following these techniques, you can effectively select data from two columns in your own SQL queries.