How to set a Where clause on the top (parent) level in PostgreSQL?-CodePudding

I'm relatively new to PostgreSQL.

My issue is that I have apartments with their reservations and I need select those are available (don't have reservations) for a given range.

Reservations table has fields apartmentId (which references Apartments table), userId and datesReserved which is tstzrange.

For these purposes I'm doing this query:

SELECT * FROM apartments 
JOIN appointments ON appointments.aparmentId = apartments.id 
WHERE NOT(datesreserved && '[2020-12-15T15:00:00.000Z, 2020-12-17T16:00:00.000Z)');

But instead I'm getting filtered appointments - those that do not contain the value [2020-12-15T15:00:00.000Z, 2020-12-17T16:00:00.000Z).

As far as I researched there are top-level where and inner-level, and as far as I understand this query should give me top-level results - those apartments that do not contain the provided range.

Am I understanding correctly or no in this case?

Thanks in advance and may God bless you!

CodePudding user response：

I think you need NOT EXISTS here. As it is, you don't have a "top level", the tables are peers.

SELECT * FROM apartments WHERE NOT EXISTS (
    SELECT 1 from appointments WHERE appointments.aparmentId = apartments.id and datesreserved && '[2020-12-15T15:00:00.000Z, 2020-12-17T16:00:00.000Z)'
);