CODEWITHSUNDEEP
sqlms-access
Black-Wolf
Black-Wolf

Reputation: 59

Select records even if doesn't exists

I have database with 4 tables:

  1. First table represents rooms and has single column called "RoomNumber".
  2. Second table is Hours and has single column "HourNumber".
  3. Third table is Days and has two columns "DayNumber" and "DayName".
  4. Last table is TimeTables of rooms and it's a relation table between the three other tables.

The last table has the following columns:

but this table stores only records with rooms that has Subject (it keep only busy hours of rooms), it doesn't presents records with empty subjects so it can't show me when a chosen room is available (no subject means the room is clear).

I want to somehow write a query that will give me the status of all the rooms in a specific day and hour.

For example this query returns all the busy rooms at day 1, hour 1:

SELECT *
FROM TimeTables
WHERE HourNumber = 1 AND DayNumber = 1

But I want to get the status of all the rooms at the specific time, I know the rest of the records doesn't exists.

Is there a way to do something like that?

Upvotes: 0

Views: 139

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

If you want the status of the rooms in MS Access, I think a correlated subquery is the simplest method:

select r.*,
       (select tt.subject
        from TimeTables as tt
        where tt.RoomNumber = r.RoomNumber and tt.HourNumber = 1 and tt.DayNumber = 1
       ) as subject_1_1
from rooms as r;

Upvotes: 1

Related Questions