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
Solved

Parameter with Left join

Posted on 2014-10-15
17
207 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
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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

860 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