How to Look Up in SQL: A Step-by-Step Guide

Introduction

When working with databases, it’s often necessary to retrieve data based on specific conditions. In this article, we’ll delve into the world of SQL and explore how to perform a “look up” query. This process is essential for extracting relevant information from your database while ensuring that only desired records are returned.

Understanding the Problem

The original question posed by the user is about finding individuals who haven’t accepted contract C1. The ADDITIONAL INFO table contains entries for each person’s contracts, but a person’s lack of acceptance for a specific contract isn’t explicitly stored in this table. Instead, if a person hasn’t accepted a particular contract, that contract won’t be present in their ADDITIONAL INFO table.

Using Common Table Expressions (CTEs)

To solve this problem, we’ll utilize a technique called Common Table Expressions (CTEs). A CTE is a temporary result set that can be referenced within a single SQL statement. In our case, the CTE will help us identify individuals who haven’t accepted contract C1.

Setting Up the Example Database

Let’s create a sample database to illustrate this concept:

-- Create the PERSON table
CREATE TABLE PERSON (
    id_person INT,
    name_person VARCHAR(50)
);

-- Create the ADDITIONAL_INFO table
CREATE TABLE ADDITIONAL_INFO (
    id_person INT,
    type_contract VARCHAR(20)
);

-- Insert data into the tables
INSERT INTO PERSON (id_person, name_person) VALUES
(1, 'John Doe 1'),
(2, 'John Doe 2');

INSERT INTO ADDITIONAL_INFO (id_person, type_contract) VALUES
(1, 'C2-BLOWSY'),
(1, 'C4-GEN'),
(1, 'C5-OLJ'),
(2, 'C2-BLOWSY'),
(2, 'C4-GEN');

Defining the CTE

Next, we’ll define our CTE:

WITH PERSON AS    (
    select 1 as id_person, 'John Doe 1' as name_person from dual union all  
    select 2 as id_person, 'John Doe 2' as name_person from dual
)

This CTE simply selects a few rows for demonstration purposes.

Defining the ADD_INFO and CONTRACTS CTEs

Now, we’ll define two more CTEs:

,
ADD_INFO AS  (
    select 1 as id_person, 'C2-BLOWSY' as type_contract from dual union all
    select 1 as id_person, 'C4-GEN'    as type_contract from dual union all
    select 1 as id_person, 'C5-OLJ'    as type_contract from dual
)
,
CONTRACTS AS (
    select 'C1-IJK'    as type_contract from dual union all
    select 'C2-BLOWSY' as type_contract from dual union all 
    select 'C3-ADF'    as type_contract from dual union all
    select 'C4-GEN'    as type_contract from dual union all
    select 'C5-OLJ'    as type_contract from dual
)

These CTEs contain sample data for demonstration purposes.

The Main Query

Now, we’ll combine the three CTEs to form our main query:

select p.id_person, p.name_person, c.type_contract
from person p,
     contracts c
where c.type_contract = 'C1-IJK'   --Search by C1 missed     
group by  p.id_person, p.name_person, c.type_contract
having (select count(*) from add_info a where a.id_person = p.id_person and a.type_contract = c.type_contract) = 0
order by p.id_person, c.type_contract;

Understanding the Query

This query works as follows:

1. It combines data from the PERSON, ADDITIONAL_INFO, and CONTRACTS CTEs.
2. It filters out rows where type_contract is equal to 'C1-IJK'.
3. It groups the results by id_person, name_person, and type_contract.
4. It applies a HAVING clause that counts the number of matches for each person’s ID in the ADDITIONAL_INFO table.
5. If there are no matches, it includes the row in the result set.

The Result

The final result is:

As expected, both individuals are included in the result set because they don’t have a contract for 'C1-IJK'.

Conclusion

In this article, we’ve explored how to perform a “look up” query using SQL. We used Common Table Expressions (CTEs) to identify individuals who haven’t accepted contract C1. This technique is useful when you need to retrieve data based on specific conditions and ensure that only desired records are returned.

By following these steps, you should be able to write your own “look up” queries using SQL. Remember to always consider the structure of your database and the type of data you’re working with when crafting your query.

Last modified on 2024-05-04