The DataRelation object is a great way to join two DataTables embedded in a DataSet.

In this small example, you will see the DataRelation:
1.) Join two DataTables
2.) Pull up information from its adjoined row.

We will use the NWind.mdb for this demonstration. We will have Three controls on the form, a comboBox and a DataGridView and a Label.

The Access file can be found here:
http://www.microsoft.com/downloadS/details.aspx?FamilyID=c6661372-8dbe-422b-8676-c632d66c529c&displaylang=en

We will use two tables from Northwind: Customers, Orders.

The comboBox will be populated with CustomerName from the Customer table. We use the CustomerID for purposes of data binding. Upon each selection of the comboBox we will load the corresponding joined DataRows into a new DataTable and bind the DataGridView. Notice, we show just a few of the columns. We could have shown them all if we wanted.

Sometimes users want to know how to pull up information from the child back to the parent. We show this in an optional cell content click event. The user clicks in a cell and the address label becomes populated with the address of the parent table.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private DataSet ds;
private DataTable customers, orders;
private OleDbConnection cn;
private OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();

public Form1()
{
InitializeComponent();
ds = new DataSet();
sb.Provider = "Microsoft.Jet.OLEDB.4.0";
sb.DataSource = Path.Combine(Application.StartupPath, "NWind.mdb");
cn = new OleDbConnection(sb.ConnectionString);
FillTables();
}

private void FillTables()
{
customers = FillTable("SELECT * FROM Customers");
orders = FillTable("SELECT * FROM Orders");
AddRelations();
comboBox1.DataSource = customers;
comboBox1.DisplayMember = "ContactName";
comboBox1.ValueMember = "CustomerID";
comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged);
comboBox1.SelectedIndex = 1;
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
String customerID = comboBox1.SelectedValue.ToString();
DataRow custRow= customers.Rows.Find(customerID);
compFile = new DataTable();
compFile.Columns.Add(new DataColumn("OrderID", typeof(Int32)));
compFile.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
foreach (DataRow row in custRow.GetChildRows("Customers_Orders"))
{
compFile.Rows.Add(row.Field<Int32>("OrderID"),
row.Field<DateTime>("OrderDate"));
}
dataGridView1.DataSource = compFile;
}

/// <summary>
/// Helper method
/// </summary>
private DataTable FillTable(String sql)
{
DataTable table = new DataTable();
using (OleDbDataAdapter da = new OleDbDataAdapter(sql, cn))
{
da.Fill(table);
}
return table;
}

/// <summary>
/// Joins the two tables
/// </summary>
private void AddRelations()
{
customers.TableName = "Customers";
customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };
orders.TableName = "Orders";
orders.PrimaryKey = new DataColumn[] { orders.Columns[0] };
ds.Tables.Add(customers);
ds.Tables.Add(orders);

//Add relation
ds.Relations.Add("Customers_Orders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"],
true);
}

private void dgComp_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
Int32 orderID = (Int32)dataGridView1["OrderID", e.RowIndex].Value;
DataRow row = ds.Tables["Orders"].Rows.Find(orderID);
DataRow parentRow = row.GetParentRow("Customers_Orders");
lblAddress.Text = parentRow.Field<String>("Address");
}
}
}

Hopefully this small example will help you to navigate across joined DataTables using the DataRelation.