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.
- 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.
- Edit the CEx31aSet::GetDefaultSQL function, as shown here, to
access the Student and Enrollment tables:
CString CEx31aSet::GetDefaultSQL()
{
return _T("[Student],[Enrollment]");
}
- 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);
- In the CEx31aView::OnInitialUpdate function, set the
recordset's m_strFilter string as follows:
m_pSet->m_strFilter = "[Student].[StudentID] =
[Enrollment].[StudentID]";
- 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);