Solved

Problem with sql statement

Posted on 2014-12-02
7
162 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

861 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

25 Experts available now in Live!

Get 1:1 Help Now