Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with sql statement

Posted on 2014-12-02
7
Medium Priority
?
180 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 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