SQL - HELP ! (pretty please)

[SilverHood]

Here's the story...

I have 2 tables, Room and Resident, related by a one to many relationship (by Room No... one room can belong to many residents)

There are 20 rooms in total - rooms that have an occupant are related to the Resident table. Empty rooms have no relations.

Now, how do I write an SQL query to show me all the rooms that have no occupant. Note, that residents that have left still have a relation from the room, but I can get round that by using a "is null" statement on the date of departure field (ie, so it only shows residents still living here).

I can write a query which shows me occupied rooms numbers. If there was some way to write an SQL statement that compared this to the the list of all rooms, and then only showed rooms without a match, then that would be a posibility too.

Sorry if it's a bit muddled... not sure how to write these questions properly
Any help would be mucho appreciated

 

[Xavier]

Ok, to be honest we'd need to see your tables to get this, but first I'd guess the following.

The select statement you've already done (i.e. occupied rooms) is it something like:

SELECT roomID
FROM tbRooms INNER JOIN
tbCusotomer ON tbRooms.customerID = tbCustomer.customerID

You really need an SQL script in the scheduler to run every couple of hours, or maybe at 'checking out time' to remove relationships between room and customer. It's bad practice to leave that kind of invalid data in there. The added benefit is also that you can then just run a select statement like:

SELECT roomID
FROM tbRooms
WHERE tbRooms.customerID isnull

which will return rooms currently vacant.

Is this MySQL or MS SQL btw? If it's MySql the above isn't possible and you'll need to pester Shovel or Fatty for a solution.

Xav

 

[SilverHood]

SELECT Room.[Room No], Resident.[Resident ID]
FROM Room INNER JOIN Resident ON Room.[Room No] = Resident.[Room No]
GROUP BY Room.[Room No], Resident.[Resident ID];


Am using Access to do the basics of it, and then tuning stuff in SQL if I need to.

 

[Xavier]

Ok, well if you start off in MSSQL it will make this easier in the long run. All you'll need aside from the above is an insert statement which checks the datetime value in the column which holds the due time to checkout and insert a NULL if there isn't one already.

Incidentally, who specced the system which handles this? Is it some form of coursework? If someone was seriously going to use this then you also need to have an ASP page which handles checkout, and charges people extra if THEY don't check out in time and make the room available for cleaners/new customers.

Xav

 

[fatbusinessman]

You really need an SQL script in the scheduler to run every couple of hours, or maybe at 'checking out time' to remove relationships between room and customer. It's bad practice to leave that kind of invalid data in there. The added benefit is also that you can then just run a select statement like:

Not necessarily, no - you might want to check who has had that room in the past.

 

[Xavier]

Not necessarily, no - you might want to check who has had that room in the past.

Inherently that data belongs to the user and should be written to a transaction table, not the room table.

He basically needs a third table covering residency - an id for the residency, an id for the room, the customer, their 'rate', the arrival and departure dates, etc.

That way you could just run a join between rooms and transactions and not need to clear anything down.

Xav

 

[fatbusinessman]

Ok, here's my attempt at a solution for you:

First off, you want to have a separate table for booking as Xav says (my post was incorrect, as I misread the original problem. So you want your tables to be something like this:

ROOM (RoomNumber, ... )
RESIDENT (ResidentID, Name, .... )
BOOKING (BookingID, RoomNumber, ResidentID, StartDate, EndDate)

Then you can set up your query as follows:

SELECT Room.RoomNumber
FROM ROOM LEFT JOIN (SELECT * FROM BOOKING WHERE EndDate IS NOT NULL) AS ACTIVEBOOKING
ON ROOM.RoomNumber = ACTIVEBOOKING.RoomNumber
WHERE ACTIVEBOOKING.BookingID IS NULL;

This should give you a list of all the room numbers which don't currently have any active bookings, but I couldn't swear to it. Use at own risk

(Note: this will require MySQL 4, as MySQL 3 doesn't support subqueries)

 

[Xavier]

Surely the enddate won't generally ever be set to Null? As a rule guests don't book in indefinately - hotels ask when you're leaving so as to have someone ready to fill the room the same day.

Better to have a 'checkedout' boolean field, and the select statement on bookings which have passed their expiry datetime AND checked out to produce immediately available rooms.

You can also determine which rooms will be available at a specified date in the future that way, by passing the date to the stored procedure and omitting the 'checked out'.

Xav

 

[SilverHood]

Ok, well if you start off in MSSQL it will make this easier in the long run. All you'll need aside from the above is an insert statement which checks the datetime value in the column which holds the due time to checkout and insert a NULL if there isn't one already.

Incidentally, who specced the system which handles this? Is it some form of coursework? If someone was seriously going to use this then you also need to have an ASP page which handles checkout, and charges people extra if THEY don't check out in time and make the room available for cleaners/new customers.

Xav

It's a Systems assignment - developing a protoype system for a Retirement Home. Got a case study of what the the main problems are (paper based, mainly), and we have to build a system prototype, and then do a presentation to the owner (which will be our marker). We can leave out some features, and pretend they'll be in the finished system, but this is one of the things that are quite high priority for having in.

I see what you're saying Xav regarding having a residency table in between room and resident. Should make it a lot easier I think.
Will have a go at setting it up, and running that query you wrote fatbusinessman!

cheers btw

 

[Deadnala]

The best tool i have ever seen for mysql:

phpMyAdmin (Demo)

phpMyAdmin (Website)

greetings

 

[Xavier]

That's a little irrelevant surely? he's not using MySQL.

 

[Deadnala]

oops my bad srry

 

[fatbusinessman]

It's a Systems assignment - developing a protoype system for a Retirement Home. Got a case study of what the the main problems are (paper based, mainly), and we have to build a system prototype, and then do a presentation to the owner (which will be our marker).

In this case (and in just about every case which isn't a high-end business system) disregard what Xavier has said about using MSSQL. MySQL/PHP would probably be your best solution, as Microsoft SQL Server costs about $5000. Plus you get access to such handy technical resources as Shovel and myself (and possibly Jonty if you're really lucky)

 

[Xavier]

*pretends not to hear*

If it's an assignment, rather than what he's going to have to deploy, then license costs mean nowt.

Besides, I think you'll find it's his lecturers who will decide if MySQL is even an option, different syntaxes and capabilities, remember? For starters MySQL doesn't even offer proper stored procedures. Inline SQL BITES.

 

[SilverHood]

This assignment has to be done in MS Access sadly.... quite scary, i'm starting not to hate it so much now than I'm finally figuring out how to use it properly. My UI is really starting to looking nippy.

Still haven't gotten round to this problem, got sidetracked a bit (I got something that didnt work to work, and had to update all my forms)

We do MySQL and Oracle databases next year, but we're still learning to do UI's in Java, so we're using access meanwhile.

Next year, we get to make a system using Oracle or MySQL, with the UI being done entirely in Java.... looking forwards to it already

 

[Xavier]

So you're writing this in ASP/VBScript?

 

[SilverHood]

no, I'm doing the UI using Access wizards and macro's. It's probably all VB in the background, but I try not to look at the code, cos I have very bad experiences with VB (A-Level project... vb code corrupted on hand in+demonstration day... au reviour chance of getting an A in computing)

All the forms and stuff run of SQL queries, or the tables themselves.

 

[Xavier]

backup!





oh, and backup!





make duplicates....



backups even!!

 

[SilverHood]

Ok, here is what I have at the moment:

SELECT ROOM.[Room No]
FROM ROOM LEFT JOIN [SELECT * FROM RESIDENCY WHERE [Date of Departure] IS NOT NULL]. AS ACTIVERESIDENCY ON ROOM.[Room No] = ACTIVERESIDENCY.[Room No]
WHERE ((([ACTIVERESIDENCY].[RESIDENCY ID]) Is Null));

Pardon all the brackets and shit, but Access adds a lot of crap to it when it's created in SQL from scratch, saved, and then reopened in SQL view.

I currently get: A box saying "ACTIVERESIDENCY.RESIDENCY ID" and I'm supposed to enter something.
If I leave it blank, I get a list of all the rooms - even the ones with an active residency.

Any ideas?

 

[Panda On Smack]

I would trash all the ( in the WHERE clause since you only have 1 condition