Lesson 13: Joining Tables in SQL

So far, you have worked exclusively with a single table -- the TrnVendor table. You might have noticed that each module within Solomon IV consists of several tables. Within the Accounts Payable module, you will find the following tables: APAdjust, APDoc, APHist, APRefNbr, APSetup, APTran, and Vendor. Each table contains specific information. At times, you might need to join two or more tables to retrieve the information you need. For example, if you look at the TrnAPDoc table, you will see that it contains the vendor ID; however, it does not contain the vendor name. If, when retrieving information from the APDoc table, you want to see the vendor name, you have to join the TrnVendor table to the TrnAPDoc table.

Syntax:

Select */tablename.fieldname<mask> ...
from tablename <alias>, tablename <alias>, tablename <alias>
where tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
order by fieldname <desc>...

Explanation:

• When you join two or more tables, a good idea is to precede the field names with the table names. This is not mandatory unless the same field name is found in more than one table.
• If you precede the field name with a table name, place a period between the two names. For example, tablename.fieldname.
• You must specify which fields are being joined.
• If you do not specify which fields are being joined, the result is what is commonly referred to as a "Cartesian join" in which all rows in the first table are joined with all rows in the second table.
• You can give each table name an alias, or alternative table name. When you assign an alias, you can then refer to the table by using its alias.

Examples:Retrieve the vendor ID, the vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables.

1. In the SQL text box, type:

Select TrnAPDoc.VendId, TrnVendor.Name,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId;

2. Execute the SQL statement.

Results:
 

TrnAPDoc.VendId	TrnVendor.Name	TrnAPDoc.OrigDocAmt
TV020	BayCon Group	542.98
TV019	Bayshore Consulting	237.60
TV018	Computer Bytes	55.50
TV017	Food Four	55.50
TV016	Music Maker	55.50
TV015	No Waste Disposal	55.50
TV014	Counter Productive	100.00
TV013	Softer Software	355.00
TV012	Paper People	55.50
TV001	Wet Off Towels	55.50
TV010	The Soda Factory	55.50
TV009	Hit the Deck	55.50

---

Retrieve the vendor ID, vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables, using a table alias.

1. In the SQL text box, type:

Select a.VendId, b.Name,
a.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc a, TrnVendor b
where a.VendId = b.VendId;

2. Execute the SQL statement.

Results:
 

a.VendId	b.Name	a.OrigDocAmt
TV020	BayCon Group	542.98
TV019	Bayshore Consulting	237.60
TV018	Computer Bytes	55.50
TV017	Food Four	55.50
TV016	Music Maker	55.50
TV015	No Waste Disposal	55.50
TV014	Counter Productive	100.00
TV013	Softer Software	355.00
TV012	Paper People	55.50
TV001	Wet Off Towels	55.50
TV010	The Soda Factory	55.50
TV009	Hit the Deck	55.50
TV008	Big Tree Landscaping	55.50
TV007	Spot Out	55.50
TV006	Mosquito No Bite	55.50
TV005	Ray Block	625.00

---

Retrieve the vendor ID, vendor name, reference number, and original document amount from the APDoc and Vendor tables for Vendor V00104. Order the results by RefNbr.

1. In the SQL text box, type:

Select TrnAPDoc.VendId, TrnVendor.Name, TrnAPDoc.RefNbr,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId
and
TrnAPdoc.VendId = 'TV004'
order by TrnAPDoc.RefNbr;

2. Execute the SQL statement.

Results:
 

TrnAPDoc.VendId		TrnVendor.Name	TrnAPDoc.RefNbr	TrnAPDoc.OrigDocAmt
TV004		Bed Room Furniture, Inc.	000222	55.50
TV004		Bed Room Furniture, Inc.	000551	3600.00