SQL Check if a Record has a Reference from Another Table and if So Do Something

As developers, we often encounter scenarios where we need to perform complex queries to fetch data from multiple tables. In this article, we’ll explore how to achieve a specific requirement: checking if a record in one table has a reference from another table and performing an action based on that.

Background

For the sake of understanding, let’s consider two tables: users and friendships. The users table contains information about individual users, including their IDs, names, and passwords. The friendships table stores relationships between users, such as friendships or pending requests.

We’ll use a simplified example with only three users in the users table and three friendships in the friendships table.

Table Structure

Let’s define the structure of our tables:

Users

id   |  name   | password              
-------------------------
1    |  Dave   | 1234   
-------------------------
2    |  John   | abcd   
-------------------------
3    |  Bob    | xyz    
-------------------------

Friendships

friend_one   |  friend_two   | status              
 -------------------------------------
       1      |      1        |   me
 -------------------------------------
       2      |      2        |   me   
 -------------------------------------
       3      |      3        |   me    
 ------------------------------------
       1      |      2        | pending
 -------------------------------------
       3      |      1        | active

Query Requirements

We need to write a SQL query that:

1. Fetches all users from the users table.
2. Adds an extra column to the result set to display the friendship status with the currently logged-in user.
3. If there’s no matching friendship, displays a specific value (e.g., “null”).

Solution

We can achieve this using a combination of SQL joins and conditional logic.

SELECT u1.id, u.name, u.password,
       COALESCE(f.status, 'null') AS friend_status
FROM users u
CROSS JOIN (SELECT DISTINCT id
            FROM users) u1
LEFT JOIN friendships f ON f.friend_one = u.id  AND f.friend_two = u1.id
                        OR f.friend_one = u1.id AND f.friend_two = u.id
ORDER BY u1.id, u.id;

Let’s break down the query:

• We start by selecting all users from the users table and getting distinct IDs using a CROSS JOIN with the same table.
• We then LEFT JOIN this result set with the friendships table on two conditions:
  • f.friend_one = u.id AND f.friend_two = u1.id
  • f.friend_one = u1.id AND f.friend_two = u.id This allows us to match each user ID in the CROSS JOIN with a friendship entry in the friendships table.
• We use the COALESCE function to replace NULL values in the friend_status column with “null”.

Output

The output will be a result set that includes all users, their IDs, names, passwords, and friendship statuses.

id  name    password    friend_status
1   Dave    1234        me
1   John    abcd        pending
1   Bob     xyz         active
2   Dave    1234        pending
2   John    abcd        me
2   Bob     xyz         (null)
3   Dave    1234        active
3   John    abcd        (null)
3   Bob     xyz         me

Conclusion

In this article, we explored how to use SQL joins and conditional logic to fetch data from multiple tables. We achieved a specific requirement by checking if a record in one table has a reference from another table and performing an action based on that.

Remember to always consider the complexity of your queries and optimize them for performance. Happy coding!

Last modified on 2024-11-22