SQL select from more tables

Question

Good day,

I have 2 sql tables.

Plaatsen

 - plid
 - x
 - y

and

Reservations

 - id
 - plid
 - startdate
 - enddate

plid in reservations has a foreign key to Plaatsen.plid

What i want is :

I got 1 date (for example 2013-12-09) how can i find all plid's that are not in the table Reservations on that variabel date? so it is not between startdate and enddate

Is this possible? can anyone help ?

Answer 1

SELECT P.Plid FROM Plaatsen p 
LEFT JOIN Reservations R ON P.Plid=R.Plid 
WHERE '2013-12-09' BETWEEN startdate AND enddate AND R.id IS NULL

Answer 2

Try this:

SELECT * FROM Plaatsen WHERE plid NOT IN (SELECT plid from Reservations where :date BETWEEN startdate AND enddate)

Where :date is your control date. Don't be afraid of subquerys, SQL servers have good optimizations for it.