build apps by user

Select Rows from Two or More Tables
(Relational 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:
CustNo CustName Person Email
C01 Abc Co. Peter abc@gmail.com
C02 Bdf Co. John bdf@gmail.com
C03 Erf Co. Bob erf@gmail.com
This is Invoice table:
InvoiceNo InvoiceDate CustID Amount
V001 1/6/2018 C01 414.00
V002 1/6/2018 C02 112.00
V003 2/6/2018 C01 152.00
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:
InvoiceNo CustNo CustID CustName Person Amount
V001 C01 C01 Abc Co. Peter 414.00
V003 C01 C01 Abc Co. Peter 152.00
V002 C02 C02 Bdf Co. John 112.00
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:
InvoiceNo CustNo CustID CustName Person Amount
V001 C01 C01 Abc Co. Peter 414.00
V003 C01 C01 Abc Co. Peter 152.00

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:
InvoiceNo ItemNo Description Price Qty
V001 T01 Item 01 12.00 20
V001 T02 Item 02 14.00 5
V001 T03 Item 03 8.00 13
V002 T02 Item 02 14.00 8
V003 T01 Item 01 12.00 10
V003 T03 Item 03 8.00 4
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:
InvoiceNo CustNo CustID CustName Person Amount ItemNo Description
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.
Category Type Description
CAT01 TY01 Item 01
CAT01 TY02 Item 02
CAT02 TY01 Item 03
CAT02 TY03 Item 04
This is the Model table. It defines the Items that are included in each model.
Model Category Type Qty
M01 CAT01 TY01 2
M01 CAT02 TY01 1
M02 CAT01 TY02 1
M02 CAT02 TY01 2
M02 CAT02 TY03 4
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:
Model Category Type Description Qty
M01 CAT01 TY01 Item 01 2
M01 CAT02 TY01 Item 03 1
M02 CAT01 TY02 Item 02 1
M02 CAT02 TY01 Item 03 2
M02 CAT02 TY03 Item 04 4