SQL query to clean up/omit missing values depending on another table-CodePudding

I'm looking for a query that is able to omit certain values (which are missing in another table). Trying to explain it using an example:

Table 1 - Person

ID	Name
1	Jane
2	Joe
3	Jose

Table 2 - Schedule

Date	Employees
1/1	Jane,Joe,Jose
2/1	Alice,Jane
3/1	Joe,Bob,Jose
4/1	Alice,Bob

Expected result - missing values omitted

Date	Employees
1/1	Jane,Joe,Jose
2/1	Jane
3/1	Joe,Jose
4/1

Is that even possible to achieve with SQL, and if so, how?

Disclaimer: I do not have any impact on the design of the tables. I know that the structure is far from ideal, but there is no way to change it.

CodePudding user response：

You want a normalized schedule table. You can create that on-the-fly with a recursive query or a combination of a lateral cross join and unnesting an array that you create from the substrings. Put this in a CTE (WITH clause) and then do your aggregation.

With an array and UNNEST

with good_schedule as
(
  select s.date, e.employee_name
  from schedule s
  cross join lateral unnest(string_to_array(employees, ',')) as e(employee_name)
)
select s.date, string_agg(p.name, ',' order by p.name) as employees
from good_schedule s
left outer join person p on p.name = s.employee_name
group by s.date
order by s.date;

With a recursive CTE

with recursive good_schedule(date, employees, employee_name, pos) as
(
  select date, employees, split_part(employees, ',', 1), 1
  from schedule s
  union all
  select date, employees, split_part(employees, ',', pos 1) as employee_name, pos 1
  from good_schedule
  where split_part(employees, ',', pos 1) <> ''
)
select s.date, string_agg(p.name, ',' order by p.name) as employees
from good_schedule s
left outer join person p on p.name = s.employee_name
group by s.date
order by s.date;

Demo: https://dbfiddle.uk/A42E5oYh