SQL query to combine records from two tables with cross join approach

Data and Databases
1

I’m working with two database tables and need help combining their data. Here’s what I have:

employees table:

EmpID FullName WorkDate
2 John 15-12-2023
2 John 17-12-2023

schedule table (only has WorkDate column):

WorkDate
16-12-2023

What I want to achieve is merging these tables so the final result looks like this:

EmpID FullName WorkDate
2 John 15-12-2023
2 John 16-12-2023
2 John 17-12-2023

Basically I need to insert the missing date from the schedule table into my employees data while keeping the same ID and name values. I’ve been trying different approaches but can’t figure out the right way to do this. Any suggestions would be really helpful!

2

Here’s how to fix this - combine your employee records with the missing schedule dates. Get all unique employees, cross join them with every date from the schedule table, then union with your existing data.

WITH unique_employees AS (
    SELECT DISTINCT EmpID, FullName 
    FROM employees
),
all_combinations AS (
    SELECT ue.EmpID, ue.FullName, s.WorkDate
    FROM unique_employees ue
    CROSS JOIN schedule s
)
SELECT EmpID, FullName, WorkDate
FROM all_combinations
WHERE WorkDate NOT IN (SELECT WorkDate FROM employees WHERE EmpID = all_combinations.EmpID)
UNION ALL
SELECT EmpID, FullName, WorkDate
FROM employees
ORDER BY WorkDate;

This creates every possible employee/date combo, filters out what you already have, then combines everything.

3

hey! a cross join might help. you could do: SELECT e.EmpID, e.FullName, s.WorkDate FROM employees e CROSS JOIN schedule s WHERE s.WorkDate NOT IN (SELECT WorkDate FROM employees) UNION ALL SELECT * FROM employees. gl! :slight_smile:

4

interesting problem! Are you working with multiple employees in your actual dataset? I’m wondering if this approach scales when you’ve got dozens of workers with different schedules. Also, what happens when the schedule table has dates that don’t apply to certain employees?