Data Table Join using ADO
What are Joins
In Excel, when you want to merge two worksheet together, you use VLOOKUP. In databases, you use joins to "merge" 2 data tables together. There are different types of joins, the easiest join is a one-to-one join, meaning that the records (in both worksheets) share a common UNIQUE value.
The most basic approach to connect two data tables together. When one is used, the records from one table is connected another table using one or more common fields. For example, you can link 2 tables together using a common field such as the identification number. The records from one table must exist in the other table. If not, the records will be ommitted.
To retrieve data from the tables connected together through an inner join, place the following sql code inside the bracket Call sqlRSRun(sql code). This sample code comes from the introduction to ADO database.
Note: ID is the field that are common to the 2 data tables.
The following syntax will be more consistent if you are keen to explore complex joins
There are many instances when you wish to merge data from 2 tables where one of the tables contains all the records and the other contains only some of the records. If that is the case, then you would require an outer join. Whether it is an left or right outer join depends on whether the arrangement of the tables. In the example below, a left outer join is used because it picks up all the data from the table on the left.
The table is a visual presentation of the left outer join to facilitate understanding. The actual reference actually comes from the sql code as shown:
If you are looking at retrieving all the records from Table2 and records in Table1 that matches those in Table2 only, then you can either using a right outer join (i.e. changing the LEFT OUTER TO RIGHT OUTER ) or changing the order of the tables.
RIGHT OUTER JOIN
CHANGING THE ORDER OF THE TABLES
New! CommentsHave your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here's how...
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft