Inserting Data into Different Columns Based on Result from Another Table

Inserting Data into Different Columns Based on Result from Another Table

In this article, we will explore a common problem in database management: inserting data into different columns based on the result of another table. This involves joining two tables and manipulating the data to meet specific requirements.

We’ll start by examining the two tables involved, TABLE1 and TABLE2, and then discuss how to join them to create a new table, TABLE3.

Understanding TABLE1

Let’s begin with TABLE1. We’re told that it has two columns:

filekey
empId

These are the columns we’ll be using as the basis for our data insertion.

Table Schema

Here is an example of what the TABLE1 schema might look like:

+---------+--------+
| filekey | empId |
+---------+--------+
| A       | 1      |
| B       | 2      |
| C       | 3      |
+---------+--------+

This table has three rows, each with a filekey and an empId.

Understanding TABLE2

Now let’s move on to TABLE2. We know that it has four columns:

filekey
date
hours
designation

These are the columns we’ll be using to determine which values will be inserted into our new table.

Table Schema

Here is an example of what the TABLE2 schema might look like:

+---------+------------+-------+----------+
| filekey | date       | hours | designation|
+---------+------------+-------+----------+
| A       | 2020-01-01| 8    | Manager   |
| B       | 2020-01-02| 7.5  | Developer |
| C       | 2020-01-03| 9    | Engineer  |
+---------+------------+-------+----------+

This table has three rows, each with a filekey, a date, and corresponding values for hours and designation.

Creating TABLE3

Our goal is to create a new table called TABLE3. This table will have the following columns:

empId
designation
monday
tuesday
wednesday
thursday
friday
saturday
sunday

We’ll insert data into this table based on the result of joining TABLE1 and TABLE2.

Joining TABLE1 and TABLE2

To join these two tables, we need to match their respective columns. In this case, our join will be based on the following mappings:

table3.empId = table1.emp_id
table3.designation = table2.designation
table3.(monday-sunday) = table2.hours

We’ll use SQL to perform this join. Here’s an example of what our query might look like:

SELECT 
    t1.empId,
    t2.designation,
    CASE 
        WHEN t2.date LIKE '2020-01-%' THEN t2.hours AS monday
        WHEN t2.date LIKE '2020-02-%' THEN t2.hours AS tuesday
        WHEN t2.date LIKE '2020-03-%' THEN t2.hours AS wednesday
        WHEN t2.date LIKE '2020-04-%' THEN t2.hours AS thursday
        WHEN t2.date LIKE '2020-05-%' THEN t2.hours AS friday
        WHEN t2.date LIKE '2020-06-%' THEN t2.hours AS saturday
        ELSE NULL AS saturday
    END AS monday,
    -- Add more CASE statements for each day of the week
FROM 
    TABLE1 t1
INNER JOIN 
    TABLE2 t2 ON t1.filekey = t2.filekey;

This query uses an inner join to combine rows from TABLE1 and TABLE2. It then uses a series of CASE statements to determine which value should be inserted into each column. The LIKE operator is used to match the date patterns, and the corresponding hours are retrieved.

Inserting Data into TABLE3

Once we’ve performed our join, we can insert the data into TABLE3. Since we’re using a series of CASE statements, we’ll need to modify each statement to fit our specific needs. Here’s an updated query that includes more days of the week:

SELECT 
    t1.empId,
    t2.designation,
    CASE 
        WHEN t2.date LIKE '2020-01-%' THEN t2.hours AS monday
        WHEN t2.date LIKE '2020-02-%' THEN t2.hours AS tuesday
        WHEN t2.date LIKE '2020-03-%' THEN t2.hours AS wednesday
        WHEN t2.date LIKE '2020-04-%' THEN t2.hours AS thursday
        WHEN t2.date LIKE '2020-05-%' THEN t2.hours AS friday
        WHEN t2.date LIKE '2020-06-%' THEN t2.hours AS saturday
        WHEN t2.date LIKE '2020-07-%' THEN t2.hours AS sunday
        ELSE NULL AS sunday
    END AS monday,
    -- Add more CASE statements for each day of the week
FROM 
    TABLE1 t1
INNER JOIN 
    TABLE2 t2 ON t1.filekey = t2.filekey;

We’ve added a sunday column and updated its corresponding CASE statement to match the date pattern.

Handling NULL Values

One potential issue with this approach is that if there’s no data for a particular day of the week, the corresponding value will be NULL. To avoid this, we can use the COALESCE function to replace NULL values with a default value:

SELECT 
    t1.empId,
    t2.designation,
    COALESCE(
        CASE 
            WHEN t2.date LIKE '2020-01-%' THEN t2.hours AS monday
            WHEN t2.date LIKE '2020-02-%' THEN t2.hours AS tuesday
            WHEN t2.date LIKE '2020-03-%' THEN t2.hours AS wednesday
            WHEN t2.date LIKE '2020-04-%' THEN t2.hours AS thursday
            WHEN t2.date LIKE '2020-05-%' THEN t2.hours AS friday
            WHEN t2.date LIKE '2020-06-%' THEN t2.hours AS saturday
            WHEN t2.date LIKE '2020-07-%' THEN t2.hours AS sunday
        END, 0) AS monday,
    -- Add more COALESCE statements for each day of the week
FROM 
    TABLE1 t1
INNER JOIN 
    TABLE2 t2 ON t1.filekey = t2.filekey;

This will ensure that all values are filled in, even if there’s no data for a particular day.

Conclusion

In this article, we explored how to insert data into different columns based on the result of another table. We used SQL to join TABLE1 and TABLE2, and then manipulated the resulting data using CASE statements. By handling NULL values and adding more days of the week, we can ensure that all values are filled in.

Table Schema

Understanding TABLE2

Table Schema

Creating TABLE3

Joining TABLE1 and TABLE2

Inserting Data into TABLE3

Handling NULL Values

Conclusion

Further Reading