Solved

trouble getting all rows on left of sql statement

Posted on 2013-01-17
5
257 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 42

Expert Comment

by:zephyr_hex
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 69

Accepted Solution

by:
Éric Moreau earned 500 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 42

Expert Comment

by:zephyr_hex
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 115
Converting C# Objects into JSON file 7 37
Row-Level Security 2 19
Sql query for filter 12 21
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

19 Experts available now in Live!

Get 1:1 Help Now