[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Query - left join, rt join...


I have a form with various fields from tblLetterofCredit.
It is joined to tblBanks_Participating on tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;

Not all tblLetterOfCredit records will have a tblBanks_Participating on the key
The form will not display the record from tblLetterOfCredit if tblBanksParicipating does not have a record. Meaning that my record in tblLetterOfCredit will not be displayed.
I only have this issue if there is no matchiing record in tblBanks_Participating.
I need to show ALL records from tblLetterOfCredit and matches on tblBanks_Participating but if there is no match on tblBanks_Participating then still show that one on tblLetterOfCredit.

How can I avoid this?
I thought I could use a LEFT JOIN from tblLetterOfCredit and it will show all records even if there is not a match for tblBanks_participating.
I imagine there is some kind of trick to this.

Thank you.

Here is the SQL:
SELECT tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType

FROM tblLetterOfCredit LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
1 Solution
<I thought I could use a LEFT JOIN from tblLetterOfCredit and it will show all records even if there is not a match for tblBanks_participating

The LEFT JOIN in your query should make it behave as you have described.

Is there something else at work here, such as Form Filtering?

What is the actual recordsource of your form?  Is it this query, or is it something else that is based on this query?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
left join will do that...
but you need to make sure you don't have any other criteria on the left joined table ...
pdvsaAuthor Commented:
I am thinking I had some kind of corruption because it is working now.  

thank you

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now