Export (0) Print
Expand All
1 out of 2 rated this helpful - Rate this topic

Recordset: Performing a Join (ODBC)

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

This article explains:

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 updateable 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 ENROLL. 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 one table with the wizard, then directly modifying source code to bind columns from another table and complete the join.

For example, the student registration database for the ENROLL sample contains Instructor and Section tables.

The Instructor table contains the following columns:

  • CourseID
  • CourseTitle
  • Hours

The Section table contains the following columns:

  • CourseID
  • InstructorID
  • RoomNo
  • Schedule
  • SectionNo

Binding the Table Columns

To bind columns from both tables to a single recordset

Use the MFC ODBC Consumer Wizard from Add Class to create a recordset class based on the Course table for the join. (Use Stdreg32.mdb from the ENROLL sample as the database.)

To join the Section table, go to the recordset class' header (.H) file and manually add one or more data members corresponding to columns in the Section table. The addition might look something like the following example:

class CCourseSet : public CRecordset
// Field/Param Data

   // Field data members for columns from "Course" table:
   CString m_CourseID;
   CString m_CourseTitle;
   int     m_Hours;

   // Field data members for columns from "Section" table:
   CString m_SectionCourseID;   // give this data member a unique name
   CString m_InstructorID;
   CString m_RoomNo;
   CString m_Schedule;
   CString m_SectionNo;


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. In the case of the recordset class above, m_CourseID and m_SectionCourseID are both bound to the CourseID column.

Modifying the Implementation

Once you create the recordset class with the wizard, 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.

To modify the recordset's constructor

Go to the recordset class' implementation (.CPP) file and modify the constructor as follows:

CCourseSet::CCourseSet(CDatabase* pdb)
   : CRecordset(pdb)
   m_CourseID = _T("");
   m_CourseTitle = _T("");
   m_Hours = 0;
   // Add the following:
   m_SectionCourseID = _T("");
   m_InstructorID = _T("");
   m_RoomNo = _T("");
   m_Schedule = _T("");
   m_SectionNo = _T("");

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 CSourceSet recordset joins the Section table to the Course table, you should rewrite your GetDefaultSQL function to look something like this:

CString CCourseSet::GetDefaultSQL()
   return _T("[Course],[Section]");
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 modify the field exchange function

In the recordset's .CPP file, modify the RFX or Bulk RFX function calls in the recordset's DoFieldExchange or DoBulkFieldExchange member function. Add an RFX function call for each data member you added to the class. For information on writing your RFX functions, see the article Record Field Exchange: How RFX Works.

void CCourseSet::DoFieldExchange(CFieldExchange* pFX)
RFX_Text(pFX, _T("[Course.CourseID]"), m_CourseID);
RFX_Text(pFX, _T("[CourseTitle]"), m_CourseTitle);
RFX_Text(pFX, _T("[Hours]"), m_Hours);
// Add the following:
RFX_Text(pFX, _T("[Section.CourseID]"), m_SectionCourseID);
RFX_Text(pFX, _T("[InstructorID]"), m_InstructorID );
RFX_Text(pFX, _T("[RoomNo]"), m_RoomNo);
RFX_Text(pFX, _T("[Schedule]"), m_Schedule);
RFX_Text(pFX, _T("[SectionNo]"), m_SectionNo);

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. Separate the table name and the column name with a period. For example, see Course.CourseID and Section.CourseID in the code above. This uniquely identifies columns that have the same name but that reside in different tables.

Setting the Join Conditions with a Filter

When you create a recordset class that joins more than one table, specify a filter using CRecordset::m_strFilter or CDaoRecordset::m_strFilter to specify which columns constitute the join (this results in MFC building a SQL WHERE clause. Set the filter after you construct the recordset object, but before you call its Open member function.

The following example joins the Course and Section tables on their common CourseID column:

CCourseSet rs;
rs.m_strFilter = "Course.CourseID = Section.CourseID";
if( !rs.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.

See Also

Recordset (ODBC) | Recordset: Declaring a Class for a Predefined Query (ODBC) | Recordset: Declaring a Class for a Table (ODBC) | Recordset: Requerying a Recordset (ODBC)

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft. All rights reserved.