| :: SQL LEFT JOIN :: show all messages | Post a New Message | Post a Reply |
 Re: SQL LEFT JOIN | Post a Reply |
since: Feb 11, 2008
from: Chicago
Don't know if this will be of an assist or not, but this is how we handled building reports from multiple Allowed Tables without getting all sorts of duplicate records.
------
Here's our internal documentation showing how we do it. It's what we provide as "Help" to our clients who use the ASP ReportWriter for ad hoc report/digital file creation in a browser based applications environment:
Documentation starts here:
----------
DUPLICATE FIELD OPTIONS FOR MULTIPLE TABLE SELECTIONS
In the beginning we made note of duplicate Field options. Here we discuss how to link your Table Fields together to reduce errors in your report.
You will notice at the bottom of the Field window (see above photo) an area labeled “Enter your tables’ relationship (Join fields)”. In this area you are required to key in the following code that will allow your Fields to be joined for each Table they are showing in.
[Table Name].[Field Name]=[Table Name].[Field Name]
For more than one Table sharing more than one Field enter in the following code:
[Table Name].[Field Name]=[Table Name].[Field Name] AND [Table Name].[Field Name]
Keep using the AND command until you have linked all of the corresponding Fields.
Let’s say you have multiple Fields in multiple Tables and you need to conjoin them, you would use the following code as many times as needed:
[Table Name].[Field Name]=[Table Name].[Field Name] AND [Table Name].[Field Name]=[Table Name].[Field Name]
In some cases you may find that all of the Field items for one particular Table may appear in other Tables that you have selected. In this instance you would remove the smaller Table from your choices to avoid unnecessary coding and to save you time.
---------
End Documentation.