The Old Joel on Software Forum: Part 4 (of 5)

SQL-question

I have one table with hotel rooms. When a room gets reserved I make an entry in another table (tblReservations) that hold a reservation ID, start and end date, and the room ID.

How do I find out which rooms does not have a reservation for a specific date?

I suspect it would beging like so:

SELECT roomID FROM tblRooms
INNER JOIN tblReservations ON tblRoom.ID = tblReservation.roomID
WHERE ...

then what?
Please help.

Hari
Saturday, April 17, 2004

SELECT roomID FROM tblRooms
LEFT JOIN tblReservations ON tblRoom.ID = tblReservation.roomID
WHERE tblReservation.roomID IS NULL

Damian
Saturday, April 17, 2004

That was fast! Thanks alot!

Hari
Saturday, April 17, 2004

Doing a left join and testing for null will bring most databases to its knees for non-trivial amounts of data (SQL Server is especially poor in this case).

A sub-select works better:

SELECT * FROM ROOMS
WHERE roomID NOT IN
(SELECT roomID from Reservations where StartDate >= @StartDate AND EndDate <= @EndDate)

Granted, you'd have to have an awful lot of rooms :)

Canuck
Saturday, April 17, 2004

Well fine... if you want to get picky :)

Damian
Saturday, April 17, 2004

Actually, I wouldn't have bothered to mention it, expcept that I am building a reservation system at the moment myself. On the surface, I didn't really think that there would be that many records to deal with ... until I started unit testing.

5 Sites
200 rooms/ site
3 years worth of data

5 * 200 * 365 * 3 = 1 095 000

For every reservation, I am storing some additional line detail information:

~10 detail lines/reservation * 1 095 000

Whamoo ... 10 million rows. Yikes ... didn't see that coming when I started.

Canuck
Saturday, April 17, 2004

I think that if you have your table indexed right, the left join solution would work just fine; even on a large set of records.

Anon-y-mous Cow-ard
Saturday, April 17, 2004

NOT IN on a subselect will generally be much faster than a where is null.

Hari, I strongly recommend learning how to use the profiler in Query Analyzer (funny isn't it - we all learn to use Query Analyzer to write stored procedures and the reason for its name has to be pointed out to us...)

Canuck, first of all you shouldn't have 3*365 for every room, though it may be close (interesting question - what's the average occupancy percentage and length of stay?)

Also, I don't get the "ten detail lines" - if they're discrete pieces of data, why not put them in the record? Or is it for things like additional charges (movies, phone calls, etc)?

Philo

Philo
Saturday, April 17, 2004

I've found NOT EXISTS is almost always faster than NOT IN, using a LEFT JOIN is sometimes faster and sometimes not.

It also depends on the DBMS, the OP never said he was using SQLServer; a lot of web sites are running MySQL 3.23 which doesn't support subselects.

Tom H
Saturday, April 17, 2004

Canuck, how's Fidelio doing now-a-days? Or are you with them ;) ? They were the ones with the "Feature Rich" package.

Since you are a Canuck I'll go on record and say I was more comfortable with Squirell. <g>

KayJay
Saturday, April 17, 2004