I'm a little out of practice on my SQL and I've been asked to create a query on a database that I can't figure out.
It's a hostel and the database is room (well, all client record) tracking.
There's a Table for Beds (500 rows)
BedID
bedname
etc.
Then there's a BedUsage table (18,000 rows)
BedUsageID
BedID
ArrivalDate
DepartureDate
ClientID
The Client Table consists of (14,000 rows)
ClientID
Firstname
Lastname
The method of determining if a bed is occupied is as simple as
select bedid from bedusage where departuredate is null
However what I need is a list of all beds (bedname) that are currently NOT occupied along with the LAST occupant (lastname and firstname) in the bed.
Any help would be appreciated.
It's a hostel and the database is room (well, all client record) tracking.
There's a Table for Beds (500 rows)
BedID
bedname
etc.
Then there's a BedUsage table (18,000 rows)
BedUsageID
BedID
ArrivalDate
DepartureDate
ClientID
The Client Table consists of (14,000 rows)
ClientID
Firstname
Lastname
The method of determining if a bed is occupied is as simple as
select bedid from bedusage where departuredate is null
However what I need is a list of all beds (bedname) that are currently NOT occupied along with the LAST occupant (lastname and firstname) in the bed.
Any help would be appreciated.