trouble getting all rows on left of sql statement

I have this sql statement.  I need to show all the rows from units, and attach the lease table for that unit.  If there is not a lease for the unit I should get nulls.  No matter how I slice I only get records that exist together in both tables.  How can I get all the units?

Here is script

SELECT     a.PropertyId, a.BldgId, a.UnitId, a.UnitType, a.UnitSubType, a.OccuStatus, b.ResiLastName, b.ResiStatus
FROM         Unit AS a LEFT OUTER JOIN
                      Lease AS b ON a.PropertyId = b.PropertyId AND a.BldgId = b.BldgId AND a.UnitId = b.UnitId
WHERE     (a.PropertyId IN
                          (SELECT     code
                            FROM          locationList
                            WHERE      (a.UnitType <> 'GA') AND (LocationID = '6') AND (a.UnitStatus = 'N'))) AND (b.ResiStatus <> 'X') AND (b.ResiStatus <> 'P')
ORDER BY a.PropertyId, a.UnitId
mgmhicksAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
remove the conditions concerning your lefted join table from the WHERE clause.

your query should read:
SELECT     a.PropertyId, a.BldgId, a.UnitId, a.UnitType, a.UnitSubType, a.OccuStatus, b.ResiLastName, b.ResiStatus
FROM         Unit AS a
LEFT JOIN Lease AS b
ON a.PropertyId = b.PropertyId
AND a.BldgId = b.BldgId
AND a.UnitId = b.UnitId
AND (b.ResiStatus <> 'X')
AND (b.ResiStatus <> 'P')
WHERE     (a.PropertyId IN
                          (SELECT     code
                            FROM          locationList
                            WHERE      (a.UnitType <> 'GA') AND (LocationID = '6') AND (a.UnitStatus = 'N')))
ORDER BY a.PropertyId, a.UnitId
0
 
zephyr_hex (Megan)DeveloperCommented:
it may be in the where part of your clause.  you may need to allow for null values in the Lease table.

one way to test is to run your query without the where clause.  do you get your left outer join as expected?
0
 
mgmhicksAuthor Commented:
Yes, but now I have multiple records for the unit.   I need a single list of the units and the leases name if there is a current lease to the unit, and a null if there is not.
0
 
zephyr_hex (Megan)DeveloperCommented:
if you need a single list, you could do a SELECT DISTINCT
0
 
mgmhicksAuthor Commented:
thank you that did it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.