Recordset: Performing a Join (ODBC)

Article
09/15/2006

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains:

What a join is.
How to perform a join of multiple tables.

What a Join Is

The join operation — a common data-access task — lets you work with data from more than one table using a single recordset object. Joining two or more tables yields a recordset that can contain columns from each table, but appears as a single table to your application. Sometimes the join uses all columns from all tables, but sometimes the SQL SELECT clause in a join uses only some of the columns from each table. The database classes support read-only joins but not updatable joins.

The key to a join operation is one or more columns that the tables have in common. For example, suppose there is a “CourseID” column in both the Course table and the Section table for an application such as the ENROLL tutorial. In the Course table, the CourseID column contains a unique ID value for each possible course. In the Section table, the CourseID column probably doesn’t contain unique values, since each course usually has more than one section.

To select records containing columns from joined tables, you need the following items:

A table list containing the names of all tables being joined.
A column list containing the names of all participating columns. Columns with the same name but from different tables are qualified by the table name.
A filter (SQL WHERE clause) that specifies the column(s) on which the tables are joined. This filter takes the form “Table1.KeyCol = Table2.KeyCol” and actually accomplishes the join. For the ENROLL example above, the filter is:
```
Course.CourseID = Section.CourseID
```

Performing the Join

The following procedure shows a join of two tables but can apply to joins of any number of tables (all on the same data source). The procedure involves first binding columns from multiple tables with ClassWizard, then directly modifying source code to complete the join.

Binding the Table Columns

To bind columns from both tables to a single recordset

Use ClassWizard to create a recordset class for the join. In ClassWizard choose Data Sources to open the Data Sources dialog box and bind columns from the first table to recordset field data members.

See the article ClassWizard: Creating a Recordset Class.
Choose ClassWizard’s Update Columns button to open the Data Sources dialog box a second time.
Select a data source and choose OK to close the Data Sources dialog box.
In the Tables dialog box, select the name of the second table and choose OK to close the dialog box.
Bind columns from the second table to additional recordset field data members.

If any column names from the second table duplicate column names from the first table, be sure to give the corresponding recordset field data members unique names. For example, if you’re joining Instructor and Section tables, each table might contain a column named RoomNo; you might bind one column to m_strInstrOffice and the other to m_strClassRoom.
Close ClassWizard.

****Note ****When creating CRecordset-derived or CDaoRecordset-derived classes with ClassWizard, be careful when selecting multiple tables or queries. Selecting multiple tables or queries will result in the construction of a join query without any restriction on how to perform the join (called a cross-product or cartesian product join). You may want to specify a filter using or (resulting in MFC building an SQL WHERE clause) before the recordset is opened. This will constrain the number of records in the result set. This is especially necessary when using the ODBC Cursor Library, since the Cursor Library may create a large temporary file for result sets with many records.

Modifying the Source Files

Once you create the recordset class with ClassWizard, you must customize two parts of the class code. First, edit the class’s table list, then qualify any columns with the same name but from different tables. You’ll need to edit the calls in your DoFieldExchange override to insert table names.

For example, the student registration database for the MFC Tutorial sample contains Instructor and Section tables. The Instructor table contains the following columns:

InstructorID
Name
RoomNo (the instructor’s office)

The Section table contains the following columns:

InstructorID
Schedule
RoomNo (where the class is held)
SectionNo
CourseID
Capacity (maximum size of the section)

To modify the recordset’s table list

Rewrite the recordset’s GetDefaultSQL member function to return a string containing a comma-delimited list of table names.

For example, if your CJoinSet recordset joins a Course table to a Section table, you should rewrite your GetDefaultSQL function to look something like this:

CString CJoinSet::GetDefaultSQL()
{
    return "SECTION, INSTRUCTOR";
}

****Tip ****As an alternative, you can pass a string containing a comma-delimited list of table names in the lpszSQL parameter when you call the recordset’s Open member function. The string has the same form as the string returned in the example above.

To qualify columns with the same name from different tables

Edit the RFX or Bulk RFX function calls in the recordset’s or member function.

For each duplicate column name, edit the second parameter in the RFX or Bulk RFX call to prefix a table name to the column name already there. Separate the table name and the column name with a period.

For example, because CJoinSet binds a RoomNo column from each table, you must modify the two RFX calls for these columns as shown in the following code:

void CJoinSet::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(CJoinSet)
    SetFieldType(pFX, CFieldExchange::outputColumn);
    RFX_Text(pFX, "Section.RoomNo", m_strClassRoom);
    RFX_Text(pFX, "Instructor.RoomNo", m_strInstructorOffice);
    // ...
    //}}AFX_FIELD_MAP
}

In the second parameter of each RFX function call above, the name RoomNo is prefixed by the table name. The two items are separated by a period.

Setting the Join Conditions with a Filter

When you construct a CJoinSet object in your program, set its filter to specify which columns constitute the join. Then call the recordset’s Open member function as shown in the following example, which joins the Instructor and Section tables on their common InstructorID column:

CJoinSet ssJoin( NULL );
ssJoin.m_strFilter = "Instructor.InstructorID = Section.InstructorID";
if( !ssJoin.Open( ) )
    return FALSE;        // recordset could not be opened

The filter supplies the connection between two columns that makes it possible to view two tables as if they were one.

You can join more than two tables in the same way by equating multiple pairs of columns, each pair joined by the SQL keyword AND.

Share via