Solved

Parameter with Left join

Posted on 2014-10-15
17
206 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 49

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 47

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 35

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 35

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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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