Query help - use joins or subqueries? 1

[zeuz]

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.

 

[gmmastros]

What have you tried so far?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeuz]

The following is ALMOST accurate, I just got to this point.

I forgot one table in the chain above, to get from bedusage to client there's a serviceused table inbetween as shown in the query below

select lastname, firstname, bedname from bed left join bedusage on bed.bedid = bedusage.bedid 
left join serviceused on serviceused.serviceusedid = bedusage.serviceusedid 
left join client on serviceused.clientid = client.clientid 
where bedusage.bedusageid in (select max(bedusageid) from bedusage group by bedid)
and bedusage.bedid in (select bedid from bed where bedid not in (select bedid from bedusage where departuredate is null)) order by bedname

This however only shows rooms that have become vacant, not those that have never been occupied. There's 2 problems with this query so far

1. it's ugly, there has to be a better way than nestled subqueries in the where clause
2. This returns 44 rows for me, that's every room that is empty that once was occupied, however any room that has never been occupied does not show up on this list.

I will continute working on this.

Thanks

 

[Andel]

i'm guessing if the bed is not occupied then departuredate would be > blank or is not null?

so can you try this.

select b.bedname, c.firstname, c.lastname
from beds b join bedusage bu on b.bedid = bu.bedid
join client on bu.clientid = c.clientid
where bu.DepartureDate > '' -- or you can use 'is not null'

 

[gmmastros]

How about this?

Select LastName, FirstName, BedName
From   BedUsage
       Inner Join (
         Select BedId, Max(BedUsageId) As MaxBedUsageId
         From   BedUsage
                Inner Join (
                  Select Beds.BedId
                  From   Beds
                         Left Join (
                           Select BedId
                           From   BedUsage
                           Where  DepartureDate Is NULL
                           ) As Occupied
                           On Beds.BedId = Occupied.BedId
                  Where    Occupied.BedId Is NULL
                  ) As Unoccupied
                  On BedUsage.BedID = Unoccupied.BedId
         Group By BedUsage.BedId
         ) as A
         On BedUsage.BedId = A.BedId
         and BedUsage.BedUsageId = A.MaxBedUsageId
       Inner Join Beds
			On BedUsage.BedId = Beds.BedId
       Inner Join ServiceUsed
			On ServiceUsed.ServiceUsedId = BedUsage.ServiceUsedId
       Inner Join Client
            On ServiceUsed.ClientId = Client.ClientId

Because this is nested so deep, it may be better to use table variables to store intermediate results. Only testing it both ways would tell for sure.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeuz]

Andel

You query lists all the previous occupants of every bed, a combined total of 17330 rows.

It needs to be only the previous occupant of beds that are currently empty, or no name but still with the bed if there's never been an occupant in that bed.

gmmastros

Your query is again almost correct, however it also doesn't take into account that there's rooms that have never been occupied so is returning what looks to be an identical result as my previous post. It's missing those beds that have never been used so don't have a bedid in the bedusage table.


Currently the closest I've gotten is this UGLY beast of a query.

select bed.bedname, lastname, firstname from bed 
left join 
  (select firstname, lastname, bed.bedid from 
    (select * from bedusage where bedusageid in 
      (select max(bedusageid) bedusageid from bedusage 
        group by bedid)) bedusage
  left join serviceused 
    on serviceused.serviceusedid = bedusage.serviceusedid 
  left join client 
    on serviceused.clientid = client.clientid 
  left join bed 
    on bed.bedid = bedusage.bedid
  where bedusage.bedid in 
   (select bedid from bed 
     where bedid not in 
      (select bedid from bedusage 
         where departuredate is null))) subq 
 on subq.bedid = bed.bedid 
 where bed.bedid in 
  (select bedid from bed 
    where bedid not in 
     (select bedid from bedusage 
         where departuredate is null))

I'm checking now to see if it is actually accurate.

 

[gmmastros]

If the bed has never been used, then it can't possibly have a last name or first name associated with it.

Anyway... this may be a good time to use union all


To include those beds that have never been used...

[green]-- Put previous query here[/green]

Union All

Select Beds.BedName, 'Never Used', 'Never Used'
From   Beds
       Left Join BedUsage
          On Beds.BedId = BedUsage.BedId
Where  BedUsage.BedId Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeuz]

I never have used union all before, but it works perfectly.

Your query, which now looks a lot nicer than mine, returns the exact rows that mine does.

Looking at the estimated execution plan, your query has a cost of 2.32, mine has 2.18. close enough that it makes no difference to me.

This query will only be executed maybe twice a day on a server that has virtually no load so I'll use yours because it looks a lot nicer.

Thanks for your help, I now have 1.5 hours to finish adding columns that are needed from the various tables and design a report around it. Relatively easy stuff now that this part is done.