Solved

trouble getting all rows on left of sql statement

Posted on 2013-01-17
5
259 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 (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 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 (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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 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

18 Experts available now in Live!

Get 1:1 Help Now