Show specific rows in SQL by not existing in another table

Question

I'm not very experienced in advanced SQL and stackoverflow, so I'm trying my best to explain what I need.

Let's say I have a table called 'Shift' and a table called 'Schedule'.

'Shift' has columns 'shift_id, shift_start, shift_end, shift_day, shift_function_id'.

shift_start represents a start time.

shift_end represents a end time.

shift_day represents the number of the day in a week (0-6 starting on sunday).

shift_function_id represents a function_id belonging to the shift.

---

'Schedule' has columns 'schedule_id, schedule_date, schedule_start, schedule_end, schedule_function_id'.

schedule_date represents the date of a schedule.

schedule_start represents start time.

schedule_end represents end time.

schedule_function_id represents a function_id belonging to the schedule.

What I'm trying to do is if a row from 'Shift' doesn't exist in table 'Schedule' with a given specific date where shift_start = schedule_start AND shift_end = schedule_end AND shift_function_id = schedule_function_id, then show the row.

Here's an example:

SELECT shift_id, shift_day, shift_start, shift_end, function_id, function_name, function_color
FROM Shift
LEFT JOIN Function
ON function_id = shift_function_id
WHERE shift_day = $day
AND NOT EXISTS (
    SELECT 1
    FROM Schedule
    WHERE schedule_date = '$date' AND schedule_start = shift_start AND schedule_end = shift_end AND schedule_function_id = shift_function_id
)
ORDER BY function_name, shift_start, shift_end;

The problem is

If I have 2 shifts with the same starting and end time and table 'Schedule' contains ONE row with the same function_id and starting and end time, BOTH the 2 shifts won't show up.

Here's an example of the table content:

Schedule

schedule_id: 310
schedule_date: 2020-01-11
schedule_start: 16:30:00
schedule_end: 20:00:00
schedule_function_id: 27

Shift

shift_id: 45
shift_day: 6
shift_start: 16:30:00
shift_end: 20:00:00
shift_function_id: 27

shift_id: 46
shift_day: 6
shift_start: 16:30:00
shift_end: 20:00:00
shift_function_id: 27

BOTH 2 rows from 'Shift' dont show up anymore'.

What I want

I want if 'Schedule' only has 1 row which contains the same information as the given data in 'Shift', I want the other row to show up.

If 'Schedule' has 2 rows with the same information, none to show up. It just needs to depend on how many rows 'Schedule' has with the same information.

IMAGES

When nothing is filled in, it shows 2 rows with same start and end

When I put a record with same start and end time, it removes both shift rows

I need this, when I only fill in one record with same start and end time

Answer 1

If your version of MariaDB supports window functions, you could use row_number() in a subquery to disambiguate records that have the same (shift_start, shift_end, shift_function) in the shift_table, or the same (schedule_start, schedule_end, schedule_function) in the schedule table.

You can then use the record rank in the join conditions (I changed the NOT EXITS subquery to a LEFT JOIN antipattern, but this is essentially the same logic).

select s.*, f.*
from (
    select 
        s.*, 
        row_number() over(
            partition by shift_start, shift_end, shift_function 
            order by shift_id
        ) rn
    from shift s
    where shift_day = @shift_day
) s
left join (
    select 
        c.*,
        row_number() over(
            partition by schedule_start, schedule_end, schedule_function 
            order by schedule_id
        ) rn
    from schedule c
    where schedule_date = @schedule_date
) c 
    on  c.schedule_start = s.shift_start 
    and c.schedule_end = s.shift_end
    and c.schedule_function_id = s.shift_function_id
    and c.rn = s.rn
left join function f
    on f.function_id = s.shift_function_id
where c.rn is null
order by f.function_name, s.shift_start, s.shift_end

Answer 2

The only way I can think of is to let NOT EXISTS clause remove all rows, Just append 1 row from duplicate rows with MIN (Or MAX) Shift_id -

SELECT shift_id, 
       shift_day, 
       shift_start, 
       shift_end, 
       function_id, 
       function_name, 
       function_color 
FROM   shift 
       LEFT JOIN function 
              ON function_id = shift_function_id 
WHERE  shift_day = $day 
       AND NOT EXISTS (SELECT 1 
                       FROM   schedule 
                       WHERE  schedule_date = '$date' 
                              AND schedule_start = shift_start 
                              AND schedule_end = shift_end 
                              AND schedule_function_id = shift_function_id) 
UNION ALL 
SELECT Min(S1.shift_id), 
       S1.shift_day, 
       S1.shift_start, 
       S1.shift_end, 
       function_id, 
       function_name, 
       function_color 
FROM   shift S1 
       JOIN shift S2 
         ON S1.shift_id <> S2.shift_id 
            AND S1.shift_day = S2.shift_day 
            AND S1.shift_start = S2.shift_start 
            AND S1.shift_end = S2.shift_end 
       LEFT JOIN function 
              ON function_id = S1.shift_function_id 
GROUP  BY S1.shift_day, 
          S1.shift_start, 
          S1.shift_end, 
          function_id, 
          function_name, 
          function_color 
ORDER  BY function_name, 
          shift_start, 
          shift_end;

Here is working example.