Solved

Parameter with Left join

Posted on 2014-10-15
17
210 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
[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
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381599
Hello,

use LEFT OUTER JOIN
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381608
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
ID: 40381611
Tried. Doesn't work. It still does not give me the blank records.
0
Technology Partners: 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 1

Author Comment

by:Vaibhavjoshi2005
ID: 40381612
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
ID: 40381615
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
ID: 40381621
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
ID: 40381633
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381638
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 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40381768
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 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40381800
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 37

Expert Comment

by:PatHartman
ID: 40382499
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
ID: 40772387
I've requested that this question be deleted for the following reason:

no good solution
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40772758
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 37

Expert Comment

by:PatHartman
ID: 40782181
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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