Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

trouble getting all rows on left of sql statement

Posted on 2013-01-17
5
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 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