Solved

Problem with sql statement

Posted on 2014-12-02
7
172 Views
Last Modified: 2014-12-02
I have the following query.

SELECT     a.ID, a.FullID, a.PropertyID, a.BldgID, a.UnitID, a.ResiID, RTRIM(b.ResiFirstName) + SPACE(1) + RTRIM(b.ResiLastName) AS name, c.TransAmt, d.DueDate
FROM         Collection_Master AS a LEFT OUTER JOIN
                      TAG_eSite.dbo.Lease AS b ON a.PropertyID = b.PropertyId AND a.BldgID = b.BldgId AND a.UnitID = b.UnitId AND a.ResiID = b.ResiId INNER JOIN
                      TAG_eSite.dbo.TransactionHeader AS c ON b.PropertyId = c.PropertyId AND b.BldgId = c.BldgId AND b.UnitId = c.UnitId AND b.ResiId = c.ResiId RIGHT OUTER JOIN
                      TAG_eSite.dbo.TransactionDetail AS d ON c.TransHeaderNo = d.TransHeaderNo
WHERE     (a.Status LIKE '%pay plan%') AND (d.IncCode = 'COLL') AND (d.JnlType = 'C')
ORDER BY a.FullID, d.DueDate DESC


my problem is that if a person doesn't have a transaction in TransactionHeader than they don't show up on the report.

So I need the list to show all persons with a.Status LIKE '%pay plan%' but I still need them to show up even if there is no transactions for them.
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
  • 2
  • +1
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40476589
> if a person doesn't have a transaction in TransactionHeader than they don't show up on the report.
> INNER JOIN TAG_eSite.dbo.TransactionHeader
Change the INNER JOIN above to a LEFT JOIN, which will include all rows to the left, and only the TransactionHeader values to the right if they exist, and if not return NULL.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476596
That was going to be my initial suggestion. But methinks they still won't show up. If they are not in TransactionHeader, then presumably they are not in TransactionDetail, and you have a WHERE requirement on that table as well.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40476598
... does call into question why there's a RIGHT JOIN on TransactionDetail ...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476617
Agree...
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40476688
Where are you trying to write the SQL?
Is it in Crystal or the database?
If it is in Crystal then as soon as you filter based on the joined table, the joins are treated as inner.

If this is in the database try putting the filter on D into the join logic also change the join to left outer

LEFT  OUTER JOIN TAG_eSite.dbo.TransactionDetail AS d ON c.TransHeaderNo = d.TransHeaderNo
AND  (d.IncCode = 'COLL') AND (d.JnlType = 'C')

mlmcc
0
 

Author Comment

by:mgmhicks
ID: 40476698
I am actually just look for the last record in the transaction detail table created for that person and getting the duedate from it.  But it does seem that no matter right or left joins, I can not get it to display someone in the collection_master table that does not have a transaction in the transactionheader table to show up.

SELECT     a.ID, a.FullID, a.PropertyID, a.BldgID, a.UnitID, a.ResiID, RTRIM(b.ResiFirstName) + SPACE(1) + RTRIM(b.ResiLastName) AS name, c.TransAmt, d.DueDate, 
                      c.TransType, c.TransHeaderNo
FROM         Collection_Master AS a INNER JOIN
                      TAG_eSite.dbo.Lease AS b ON a.PropertyID = b.PropertyId AND a.BldgID = b.BldgId AND a.UnitID = b.UnitId AND a.ResiID = b.ResiId INNER JOIN
                      TAG_eSite.dbo.TransactionHeader AS c ON b.PropertyId = c.PropertyId AND b.BldgId = c.BldgId AND b.UnitId = c.UnitId AND b.ResiId = c.ResiId RIGHT OUTER JOIN
                      TAG_eSite.dbo.TransactionDetail AS d ON c.TransHeaderNo = d.TransHeaderNo
WHERE     (a.Status LIKE '%pay plan%') AND (d.IncCode = 'COLL') AND (d.JnlType = 'C')
ORDER BY a.FullID, d.DueDate DESC

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 40476735
working on creating sql procedure to be used in crystal, so I can filter the records once I have them.  But I only want inccode = 'coll' in transdetail and and I need to list everyone on the collection_Master with "Pay Plan"  in the status.

This is what I have now  
SELECT     a.ID, a.FullID, a.PropertyID, a.BldgID, a.UnitID, a.ResiID, RTRIM(b.ResiFirstName) + SPACE(1) + RTRIM(b.ResiLastName) AS name, c.TransAmt, d.DueDate
 FROM         Collection_Master AS a LEFT OUTER JOIN
                       TAG_eSite.dbo.Lease AS b ON a.PropertyID = b.PropertyId AND a.BldgID = b.BldgId AND a.UnitID = b.UnitId AND a.ResiID = b.ResiId INNER JOIN
                       TAG_eSite.dbo.TransactionHeader AS c ON b.PropertyId = c.PropertyId AND b.BldgId = c.BldgId AND b.UnitId = c.UnitId AND b.ResiId = c.ResiId LEFT  OUTER JOIN 
                       TAG_eSite.dbo.TransactionDetail AS d ON c.TransHeaderNo = d.TransHeaderNo AND  (d.IncCode = 'COLL') AND (d.JnlType = 'C')

 WHERE     (a.Status LIKE '%pay plan%') AND (d.IncCode = 'COLL') AND (d.JnlType = 'C')
 ORDER BY a.FullID, d.DueDate DESC

Open in new window

and its still missing people without transactions.  If I remove the where clause references to d then I get 6400 records rather than 240

thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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