Top/Max won't do it - what to try next?

  • Thread starter Thread starter Dave Holmes
  • Start date Start date
D

Dave Holmes

I have the following scenario:

A table called FACILITY linked, one-to-many, to a table called
LICENSES (each facility has a history of licenses). The licenses have
and EXPIRATION_DATE field and are linked, one-to-many, to various
other tables (INVOICES, etc).

I would like to have a query that returns the FACILITY, its latest
license (determined by the EXPIRATION_DATE) and the records linked to
that license.

I've tried returning the TOP 1 record in the query or a subquery of
the licenses, but that returns only one facility (the one with the
latest license expiration). MAX returns all of the licenses, with the
latest expiration date in each one.

Can this be done? Thanks!
 
Try something along the lines of

SELECT ...........
FROM Facility F INNER JOIN (Licenses L INNER JOIN Invoices
I ON L.licenseId = I.licenseId) ON F.facilityId = L.facilityId
WHERE licenseId IN (SELECT TOP1 licenseId FROM License L2
WHERE L2.facilityId = F.facilityId ORDER BY expirationDate
DESC)

Hope This Helps
Gerald Stanley MCSD
 
A clarification - I would like the query to return ALL of the
facilities, their latest license, etc.

Thanks.
 
Back
Top