Choose One matching Result from 4 table View

Posted on 2005-05-12
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

    Author Comment

    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

    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

    Perfect!  Thanks a lot!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now