[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

trouble getting all rows on left of sql statement

Posted on 2013-01-17
5
Medium Priority
?
267 Views
Last Modified: 2013-01-17
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
0
Comment
Question by:mgmhicks
  • 2
  • 2
5 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 38787620
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
 

Author Comment

by:mgmhicks
ID: 38787633
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 38787689
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
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 38787727
if you need a single list, you could do a SELECT DISTINCT
0
 

Author Closing Comment

by:mgmhicks
ID: 38787748
thank you that did it.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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