Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Choose One matching Result from 4 table View

Posted on 2005-05-12
Medium Priority
Last Modified: 2010-03-19
I have a view I'm trying to create that contains 4 tables.  Each of these is linked by Building, Unit, Resident info.  One of the tables will have multiple entries that match building, unit, resident.  What I want is to show every response for the first 3 tables but limit the 4th table's output to just showing the most recent item.  Can it be done without pulling my hair out?  This is how I have it now but the output will show multiple results because there are multiple entries in the 4th table.

                      TOP 100 PERCENT dbo.rmunit.building, dbo.rmunit.unit, dbo.rmunit.occupied, dbo.rmunit.address, dbo.[rr-units].explanation, dbo.[rr-units].[stat-code],
                      dbo.rmunit.[market-rent], dbo.rmunit.[unit-type], Properties.dbo.pmEntity.Property, dbo.[rr-units].[eff-date] AS [ready-date]
FROM         dbo.rmunit INNER JOIN
                      Properties.dbo.pmEntity ON LEFT(dbo.rmunit.building, 2) = Properties.dbo.pmEntity.entity OR
                      LEFT(dbo.rmunit.building, 2) = Properties.dbo.pmEntity.otherent LEFT OUTER JOIN
                      dbo.[rr-units] ON dbo.rmunit.building = dbo.[rr-units].building AND dbo.rmunit.unit = dbo.[rr-units].unit LEFT OUTER JOIN
                      dbo.RentedCurVacant ON dbo.rmunit.building = dbo.RentedCurVacant.building AND dbo.rmunit.unit = dbo.RentedCurVacant.unit
WHERE     (LEFT(dbo.[rr-units].explanation, 5) <> 'ready') AND (dbo.RentedCurVacant.building IS NULL) AND (dbo.rmunit.occupied = 0) AND
                      (LEFT(dbo.rmunit.building, 1) <> '1') AND (dbo.rmunit.building <> 'CPGAR') AND (dbo.rmunit.building <> 'T1MARI') AND (dbo.rmunit.building <> 'SMCOM')
                       AND (dbo.rmunit.building <> 'SMGAR') AND (LEFT(dbo.[rr-units].[stat-code], 6) <> 'OFFLIN') AND (LEFT(dbo.[rr-units].[stat-code], 3) <> 'occ') AND
                      (LEFT(dbo.[rr-units].[stat-code], 3) <> 'vac') AND (LEFT(dbo.[rr-unit[[rs].[stat-code], 3) <> 'ntv')
ORDER BY dbo.rmunit.building, dbo.rmunit.unit, dbo.[rr-units].[eff-date] DESC

[rr-units] is the table which contains more then 1 entry per building, unit, resident combo.  Anyway to limit it?
Question by:BSitko
  • 2

Author Comment

ID: 13988481
Also if no data exists in the [rr-units] table I need the two columns (explanation and [stat-code] to show NULL)
LVL 34

Accepted Solution

Brian Crowe earned 2000 total points
ID: 13988987
try this...

SELECT DISTINCT TOP 100 PERCENT dbo.rmunit.building,
     RRUnits.[eff-date] AS [ready-date]
FROM dbo.rmunit
INNER JOIN Properties.dbo.pmEntity
     ON LEFT(dbo.rmunit.building, 2) = Properties.dbo.pmEntity.entity
     OR LEFT(dbo.rmunit.building, 2) = Properties.dbo.pmEntity.otherent
LEFT OUTER JOIN (SELECT * FROM dbo.[rr-units] A WHERE A.[eff-date] IN
     (SELECT MAX(B.[eff-date]) FROM dbo.[rr-units] B WHERE B.building = A.building AND B.unit = A.unit)) AS RRUnits
     ON dbo.rmunit.building = RRUnits.building
     AND dbo.rmunit.unit = RRUnits.unit
LEFT OUTER JOIN dbo.RentedCurVacant
     ON dbo.rmunit.building = dbo.RentedCurVacant.building
     AND dbo.rmunit.unit = dbo.RentedCurVacant.unit
WHERE (LEFT(RRUnits.explanation, 5) <> 'ready')
     AND (dbo.RentedCurVacant.building IS NULL)
     AND (dbo.rmunit.occupied = 0)
     AND (LEFT(dbo.rmunit.building, 1) <> '1')
     AND (dbo.rmunit.building NOT IN ('CPGAR', 'T1MARI', 'SMCOM', 'SMGAR'))
     AND (LEFT(RRUnits.[stat-code], 6) <> 'OFFLIN')
     AND (LEFT(RRUnits.[stat-code], 3) NOT IN ('occ', 'vac', 'ntv'))
ORDER BY dbo.rmunit.building,
     RRUnits.[eff-date] DESC

Author Comment

ID: 13990005
Perfect!  Thanks a lot!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question