microsoft_excel

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.

INNER JOIN

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

facebook-comments

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here's how...


Copyright © advanced-excel.com 2007 - 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft

>