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 ?

score 1 · Answer 1 · edited Dec 09 '13 at 18:15

1

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.

edited Dec 09 '13 at 18:15

psxls

6,807
6
30
50

answered Dec 09 '13 at 17:51

BaBL86

2,602
1
14
13

can you make a query where i see all plid from plaatsen where reservation are between dates. so instead of using 1 date use between 2 dates ? – Roboneter Dec 09 '13 at 20:29

score 0 · Answer 2 · answered Dec 09 '13 at 17:49

0

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

answered Dec 09 '13 at 17:49

Mihai

26,325
7
66
81

This won't return any rows. I leave it as an exercise to the reader to figure out why. – Kyle Hale Dec 09 '13 at 17:52

SQL select from more tables

2 Answers2