Finding available schedules for multiple slots I was asked to come up with a database schema to keep track of the slots and their schedules and query the table to find out the available time(empty schedule) for the slots.  

I created three tables 1) slots which holds all the slots 2) schedule which has all the available schedules 3) slotschedule which has all the booked schedules for the corresponding slots. 

This is the database diagram 



The corresponding DDL design is the following one:

create table Slots
(
  Id int  PRIMARY KEY 
);
create table Schedule
(
  Id int PRIMARY KEY ,
  StartTime Time,
  EndTime Time
);

create table SlotSchedule
(
  SlotId int,
  ScheduleId int,
  CONSTRAINT FK_SlotID_Id FOREIGN KEY (SlotId)     
    REFERENCES Slots(Id),
  CONSTRAINT FK_ScheduleID_Id FOREIGN KEY (ScheduleId)     
    REFERENCES Schedule(Id)  
);


The requirement is to query the available schedules which can be booked for the slots. 

The query I wrote is 

select slots.SlotId,schedule.StartTime, schedule.EndTime 
from Schedule schedule
LEFT JOIN 
SlotSchedule slots
on slots.ScheduleId = schedule.Id and slots.SlotId = 1
where slots.SlotId is null
order by 1, 2


which results in 



But when I want to find for a list of slotId say 1, 2 the result I get is this 



From which I can not infer which available schedule belongs to which slot. 

This schema and query work fine and I can find a list of available schedules for a particular slot. However, if I want to query a list of slots and gets it's corresponding available schedules I am not sure if that can be done by extending this design.

How do I get all the available schedule for a list of slots i.e. for slots = 1, 2, 3 rather than just 1 as in the query above.

The fiddle is present here https://www.db-fiddle.com/f/oS7mvCgoZipqKGz74wMcFE/0
Updated answer:

select 
    Slots.Id,
    Schedule.StartTime,
    Schedule.EndTime
from
    Slots
join Schedule on 1 = 1
left outer join SlotSchedule on Slots.ID = SlotSchedule.SlotId and Schedule.Id = SlotSChedule.ScheduleId
where SlotSchedule.SlotID is null;


... and here is the Fiddle.

This is perhaps not an orthodox approach. It does join the Slot and Schedule tables fully which may have serious performance issues on large tables - but I am guessing the number of rows in Schedule is tiny, and probably in Slot as well.

What this does:

(First, note, I would not use Slots as an alias for SlotSchedules when you also have a table named Slots - that's just way too confusing.)

Start with the Slots table. I then join this to the Schedule table (that's right - totally skipping the SlotSchedule table) on 1=1 - this means every row in Slots will join to every row in Schedule. 

The next join is back to the SlotSchedule table and it is an outer join. This time we have conditions - we say that the Slot ID on the SlotSchedule must match our Slot record, and the Schedule ID on the SlotSchedule table must match our Schedule record - but because it is an outer join, if there is no match, we still include the SlotSchedule row, but with a value of null.

This sets us up for the WHERE clause, which is simply - we want all rows where the SlotSchedule information is null, and so we check SlotSchedule.SlotID is null. 

In other words, for this combination of Slot and Schedule records, there is no SlotSchedule record, which is what we want.



Original answer:

Your where clause has:

where slots.SlotId is null


Your diagram shows:

slots.id


This is inconsistent.

Your results show slot_id = null (and your query requests this).

I don't understand how you can have slot records with null ID. It looks like you are trying to populate slotschedule with a row for every possible combination of slot and schedule, and to keep the ID null if the slot currently has no schedule.

I would recommend slotschedule only has a row when a slot is assigned to a schedule. 

The first thing I would want to understand better is why slot_id is null - I think change this design to resolve the issue.