Please Help with advanced sql query

mstuart60 · June 22nd, 2003, 09:24 AM

I have a database that shows seat utilisation in a computer class. We hold 3 sessions daily, ie am, pm, evening. On the booking they can book for any seesion and have a seat also a seat number 1 -8. I want to be able to create a query that will eventually show up on a web page , which shows that session = am has some seats booked and some available. The session data lies in a session_booking table, that contains the following columns.
Session_date, Session _name, seat number, booked by
01/04/2003, am, 1, voucher
01/04/2003, pm, 5, voucher
01/04/2003, evening, 3, Club

I dont really want to re-organise the table.

I would like this solved sharply as i have been at it for about 3 weeks.

Mike

Jeff Mason · June 22nd, 2003, 03:19 PM

First off, it is helpful in this sort of situation to have a table of the valid seat numbers. I'll assume the existence of this table; we'll call it SeatNumbers; it has a single column 'SeatNumber'.

Assuming you provide the date and session name as parameters, then a query which lists the available seats for a given date and session is:

Code:

-- @sd is the session date, @sn is the session name

SELECT @sd, @sn, SeatNumber
   FROM SeatNumbers
   WHERE SeatNumber NOT IN (SELECT seat_number FROM session_booking SB
            WHERE SB.session_date=@sd
              AND sb.session_name=@sn);

I find it very handy to have a sequence number table available for these sorts of purposes. I create a table of integers and populate it with the numbers from 1 to how ever many I think I need. I use them for all sorts of random numbering purposes like this.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com