Solved

Problem with sql statement

Posted on 2014-12-02
7
156 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
Comment Utility
> 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
Comment Utility
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
Comment Utility
... does call into question why there's a RIGHT JOIN on TransactionDetail ...
0
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

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Agree...
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

11 Experts available now in Live!

Get 1:1 Help Now