Joining Two Database Tables

Most database programmers know that a join is one big logical table composed of fields from two or more related tables. In the Student Registration database, you could join the Student table with the Enrollment table to get a list of students and the classes they are enrolled in.

Joins are easy to do with Visual C++ because ClassWizard lets you add tables to an existing recordset. A few additional programming tasks are needed, though. Here are the steps for joining the Enrollment table to the Student table in EX31A.

1. Use ClassWizard to access the CEx31aSet class on the Member Variables tab. Click the Update Columns button, and then select the Enrollment table from the Student Registration database. If you get a warning message indicating that the data source does not contain all the columns that the recordset classes need, click the Yes button to continue. Then click the Bind All button to add the data members for the Enrollment fields.

2. Edit the CEx31aSet::GetDefaultSQL function, as shown here, to access the Student and Enrollment tables:

   CString CEx31aSet::GetDefaultSQL()
   {
       return _T("[Student],[Enrollment]");
   }

3. Two StudentID fields are now in the joined table. In the CEx31aSet::DoFieldExchange function, edit the StudentID line to qualify the field with a table name:

   RFX_Long(pFX, _T("[Student].[StudentID]"), m_StudentID);

4. In the CEx31aView::OnInitialUpdate function, set the recordset's m_strFilter string as follows:

   m_pSet->m_strFilter = "[Student].[StudentID] =
       [Enrollment].[StudentID]";

5. In the CEx31aView::OnDraw function, add code to display the new Enrollment fields. Here is a sample:

   pDC->TextOut(pText.x+5000, pText.y, m_pSet->m_CourseID);