Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with sql statement

Posted on 2014-12-02
7
Medium Priority
?
175 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 66

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 66

Expert Comment

by:Jim Horn
ID: 40476598
... does call into question why there's a RIGHT JOIN on TransactionDetail ...
0
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.

 
LVL 24

Expert Comment

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

Accepted Solution

by:
mlmcc earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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