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.

Table1.IDTable2.IDUsing Inner Join
61345 omitted
67554 67554included
 34890omitted


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.

Select * from Table1, Table2 
where Table1.ID=Table2.ID 
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

Select * from Table1 Table1 
INNER JOIN 
Table2 Table2 on Table1.ID=Table2.ID 

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.

Table1.IDTable2.IDUsing Inner Join
61345 included
67554 67554included
 34890omitted

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:

Select * from Table1 Table1 
LEFT OUTER JOIN 
Table2 Table2 on Table1.ID=Table2.ID 

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

Select * from Table1 Table1 
RIGHT OUTER JOIN 
Table2 Table2 on Table1.ID=Table2.ID 

CHANGING THE ORDER OF THE TABLES

Select * from Table2 Table2 
LEFT OUTER JOIN 
Table1 Table1 on Table1.ID=Table2.ID 

Back to ADO




New! Comments

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