Solved

Parameter with Left join

Posted on 2014-10-15
17
204 Views
Last Modified: 2015-05-17
I have created a left query and on the second table I also provide a parameter . I now need to show all records which correspond to that month as well as all records from first table for which there is no record for that month in second table.

However the query does not output it for 2nd part. How do I get this.

SQL is as

SELECT Organisation.[UEN Number], Organisation.Name1, Organisation.Channel, Audit_Profile.[Risk Category], Audit_Profile.[Country of promoter], Audit_Profile.[Number of promoters], Audit_Profile.[Annual Turnover KYC], Audit_Profile.[Monthly incoming KYC], Audit_Profile.[Monthly outgoing KYC], Audit_Bank_review.[For month], Audit_Bank_review.Bank, Audit_Bank_review.[Acct currency], Audit_Bank_review.[Total deposits], Audit_Bank_review.[Total withdrawals], Audit_Bank_review.[Single largest TX], Audit_Bank_review.[Opening balance], Audit_Bank_review.[Closing Balance]
FROM (Organisation INNER JOIN Audit_Profile ON Organisation.[UEN Number] = Audit_Profile.[UEN Number]) LEFT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number]
WHERE (((Organisation.Channel)="Foreign incorporation") AND ((Audit_Bank_review.[For month])=[Which month?]));

So in this query - if I input month as "September" I expect all records for september + blank records to be displayed. But it is not happening.
0
Comment
Question by:Vaibhavjoshi2005
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
Hello,

use LEFT OUTER JOIN
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
It will only do that where Organisation.Channel="Foreign incorporation"; how many records do you have with that?
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
Comment Utility
Tried. Doesn't work. It still does not give me the blank records.
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
Comment Utility
100 records as of now. I dont have problem with Channel. But what I want is display all records for month September + all records for which there is no record in review table for september.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Two questions:

1. What sort of values do you have in Audit_Bank_review.[For month]? If it is 1-Sep-2014, then you have to type 1-Sep-2014.

2. I think you need a RIGHT JOIN instead of a LEFT JOIN.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
You also need to change

((Audit_Bank_review.[For month])=[Which month?]))

to

((Audit_Bank_review.[For month])=[Which month?]) OR Audit_Bank_review.[For month] IS NULL)
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
Comment Utility
Here is more explanation;
Organisation table states the details of organization and audit review stores record of their bank transactions for month (By various months – like august, September, October etc). So there is one to many relationship.
Now if I execute for the month of September I want to see all records from Audit review with September month in record + all organisations for which there is no September record.

I can not use

((Audit_Bank_review.[For month])=[Which month?]) OR Audit_Bank_review.[For month] IS NULL)
Because an organization may have record for August but not for September (which will not be returned in this case)

I can not use right join, as I can not see blanks in this case .
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
How about this? Change:

LEFT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number]
WHERE (((Organisation.Channel)="Foreign incorporation") AND ((Audit_Bank_review.[For month])=[Which month?]));

to

RIGHT JOIN Audit_Bank_review ON Organisation.[UEN Number] = Audit_Bank_review.[UEN Number] AND Audit_Bank_review.[For month])=[Which month?]
WHERE (((Organisation.Channel)="Foreign incorporation");
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
In such cases I create a separate query:

Select
    Audit_Bank_review.[UEN Number]
From
    Audit_Bank_review
WHERE
    Audit_Bank_review.[For month] = [Which month?]

Save this and use it in the main query with the Left outer join.

/gustav
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
I use the same technique as Gustav, but rather than saving the query, I simply make it a subquery.

SELECT A.*, B.*
FROM A
LEFT JOIN (
SELECT *
FROM yourTable
WHERE [someField] = "SomeValue"
) as B
ON A.KeyField = B.KeyField

The reason the LEFT OUTER JOIN does not work is that as soon as you ADD the Criteria to the 2nd table, that negates the OUTER portion of the JOIN.  Another way to do this which might return WHAT you are looking for is:

SELECT A.*, B.*
FROM A
LEFT JOIN B
ON A.JoinField = B.JoinField
WHERE ((B.SomeField IS NULL) OR (B.SomeField = "SomeValue"))
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The separate query suggested by Gus or the sub query suggested by Dale both work.  The REASON you need to do this is because of the way joins work and when criteria is applied.  So, the database engine is performing the Left Join FIRST and then applying the criteria to the many-side table.  That is why you are losing the empty records.  To get around this, you need to coerce the query engine into applying the many-side criteria FIRST and then doing the join so you apply the criteria in a separate query or subquery and then left join to the query.  That preserves the intention of the left join.

Without knowing the reason, you can't ever apply what we tell you correctly in other situations.  So, in summary, if you need a Left join and you have criteria on the many-side, you need to separate it to force the criteria to be applied first and then do the join.  There is no problem with criteria on the many-side table in an inner join because you are only trying to return rows that match.
0
 
LVL 1

Author Comment

by:Vaibhavjoshi2005
Comment Utility
I've requested that this question be deleted for the following reason:

no good solution
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
No good solution?
Gus and Dale gave you query options and I explained WHY the problem exists.  Just because you don't like the answer doesn't mean it isn't a good solution.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I think either Dale or Gus' solution will work since the point is to isolate the criteria being applied to the table of the right join BEFORE the join takes place.  So you can split between them.  If you are feeling generous, you can give me points for explaining WHY this must be done.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

12 Experts available now in Live!

Get 1:1 Help Now