Select from two tables

Hi! I have two tables. available(id, nrRooms) , booked(Bid, StartDate, EndDate)

I would like to select id, nrRooms from available where the id is not equal to Bid and date between StartDate and EndDate. date comes as a parameter.

example

available
id nrRooms
1 3
2 4
3 5

booked
Bid StartDate EndDate
1 13 15

if my date is 14, I would like to receive
id nrRooms
2 4
3 5

I’ve tried with

SELECT available.id, available.nrRooms FROM available, booked where 
available.id <> booked.Bid OR available.id = booked.Bid AND date NOT 
BETWEEN StartDate AND EndDate

SELECT 
    available.id
  , available.nrRooms 
FROM available
LEFT OUTER JOIN booked 
ON available.id = booked.Bid 
AND date NOT BETWEEN StartDate AND EndDate
WHERE booked.Bid IS NULL

To get rows from the first table that are not present in the second table, you have to use a LEFT JOIN

Try


SELECT
    available.id
  , available.nrRooms
FROM available
LEFT OUTER JOIN booked
ON available.id = booked.Bid
AND @StartDate <= EndDate
AND @EndDate >= StartDate
WHERE booked.Bid IS NULL 

The idea is to get all booked rooms in the period you want, and then get only those rooms from ‘available’ that you didn’t get from the second table (i.e. where the left join returns NULL for the booked table columns).

Problem again…

I want to show only available rooms, so I tried to extend the query to this

SELECT
available.id
, available.nrRooms
FROM available
LEFT OUTER JOIN booked
ON available.id = booked.Bid
AND (@StartDate BETWEEN StartDate AND EndDate) OR
(@EndDate BETWEEN StartDate AND EndDate) OR
(StartDate BETWEEN @StartDate AND @EndDate) OR
(EndDate BETWEEN @StartDate AND @EndDate)
WHERE booked.Bid IS NULL

But my paranthesis is removed automaticaly, so I don’t think this is the right way?
I have two parameters now, @StartDate and @EndDate. I dont want to show a room that is booken between these dates, and I dont want to show a room that is booked before @StartDate and that is booked until after @EndDate.

I would be happy if anyone could help me in this question

Thanks!
Now it works as I want to! I’ve been trying a few hours, so I’m happy for the solution you gave me!

Sorry, I missed the NOT…
change ‘NOT BETWEEN’ in ‘BETWEEN’

Thanks for the help with LEFT OUTER JOIN, but it still doesn’t work as I want to.

Right now the sql select look like this

SELECT available.Id, available.nrRooms
FROM available LEFT OUTER JOIN
booked ON available.Id = booked.BId AND 7 NOT BETWEEN booked.StartDate AND booked.EndDatum
WHERE (booked.BId IS NULL)

booked look like this
BId, StartDate, EndDate
1 13 16
4 11 18
4 18 20
3 6 8

In this case I would like to get all rooms from available that is not booked for this date,
so I would like to get
available with id 1,2,4,5,6 but what I get is available with id 2,3,5,6