= Select Rows from Two or More Tables (Relational Tables)
> Replacing Values in Formula
> SQL Functions for Manipulating Data
Select Rows from Two or More Tables
One powerful and flexible feature of SQL is that it allows you to select data from two or more tables together. When one table is “related” to another table, it is convenient to use a single formula to select rows of data from the two tables together.
Relate 2 Tables Together
Two tables are related when they share one or more common fields, or columns of data. The two related tables can be “joined” together to make it like a single table.
The example below illustrates how to select rows of data from two related tables.
This is Customer table:
This is Invoice table:
Customer.CustNo and Invoice.CustID are the common columns of the two tables. The names of common fields are not necessarily the same. Although the field names are different, the stored data are referring to the same set of Customer IDs. It is therefore Customer and Invoice tables can be “related”.
This formula retrieves data from the two tables:
Select InvoiceNo, Customer.CustNo, Invoice.CustID, CustName, Person, Amount From Customer, Invoice Where Customer.CustNo = Invoice.CustID
The expression in the Where clause, Customer.CustNo = Invoice.CustID, is the condition that “joins” the 2 tables together. Only the rows from Customer and Invoice tables that have the same Customer IDs are selected in the output result:
From the output result, you can see these points:
With the condition, the 2 tables are joined together and become a single table in the output result.
Only the rows from the two tables that have the same Customer IDs are selected.
Since there are 2 records in Invoice table that belong to Customer C01, there are 2 rows in the output result. The data of the other columns – CustName and Person of C01 from Customer table are duplicated in the output result.
For Customer C03, since there is no Invoice record related to it, it is not selected in the output result.
You can further confine the output result by adding more conditions:
Select InvoiceNo, Customer.CustNo, Invoice.CustID, CustName, Person, Amount From Customer, Invoice Where Customer.CustNo = Invoice.CustID And Customer.CustNo = "C01"
The condition Customer.CustNo = “C01” is added in the Where clause, and the output result only selects the rows for Customer C01:
Relate 3 Tables Together
Sometimes you may want to relate 3 tables together. Let’s say there is another table InvoiceItem which holds the item records for each Invoice:
Both tables – Invoice and InvoiceItem have the same column – InvoiceNo which hold the Invoice Nos., and therefore they can be related.
In order to include the data of InvoiceItem in the output result, another condition is added in the formula:
Select Invoice.InvoiceNo, Customer.CustNo, Invoice.CustID, Customer.CustName, Customer.Person, Invoice.Amount, InvoiceItem.ItemNo, InvoiceItem.Description From Customer, Invoice, InvoiceItem Where Customer.CustNo = Invoice.CustID And Customer.CustNo = "C01" And Invoice.InvoiceNo = InvoiceItem.InvoiceNo
In the From clause – Customer, Invoice, InvoiceItem, the 3 tables are listed together.
The condition Invoice.InvoiceNo = InvoiceItem.InvoiceNo in the Where clause relates the rows from Invoice and InvoiceItem tables together.
The output result is as below:
|V001||C01||C01||Abc Co.||Peter||414.00||T01||Item 01|
|V001||C01||C01||Abc Co.||Peter||414.00||T02||Item 02|
|V001||C01||C01||Abc Co.||Peter||414.00||T03||Item 03|
|V003||C01||C01||Abc Co.||Peter||152.00||T01||Item 01|
|V003||C01||C01||Abc Co.||Peter||152.00||T03||Item 03|
Since there are 3 Items for Invoice V001, there are 3 rows in the output result. The Invoice and Customer data are repeated across the rows.
The same happens for Invoice V003.
Since the field name InvoiceNo occurs in both Invoice and InvoiceItem tables, in the formula Invoice.InvoiceNo is used to specify that it is the column from the Invoice table.
Relate Tables with 2 or More Common Columns
Sometimes, one common column is not enough to relate tables together. In this case, more columns shall be included in the conditions in Where clause.
This is the Item table. It is the master table of Items. Each row of it represents a different item.
This is the Model table. It defines the Items that are included in each model.
The common columns of the 2 tables are Category and Type. It is because either using Category or Type only in Item table is not sufficient to define an Item. It is called the Uniqueness of the table.
To relate the two tables together, this formula is used:
Select Model, Item.Category, Item.Type, Description, Qty From Item, Model Where Item.Category = Model.Category And Item.Type = Model.Type
And this is the output result: