Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.
This article explains:
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:
Course.CourseID = Section.CourseID
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.
To bind columns from both tables to a single recordset
See the article ClassWizard: Creating a Recordset Class.
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
.
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 CRecordset::m_strFilter or CDaoRecordset::m_strFilter (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.
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 ENROLL contains Instructor and Section tables. The Instructor table contains the following columns:
The Section table contains the following columns:
To modify the recordset’s table list
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
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.
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.
See Also Recordset: Declaring a Class for a Predefined Query (ODBC), Recordset: Declaring a Class for a Table (ODBC), Recordset: Requerying a Recordset (ODBC)