SQL Join

Joining Tables

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.

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.

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.

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.