How to do a SELECT from different tables

Problem/Question/Abstract:

I am trying to select from 2 or more tables and I am unsuccessful. How would I go about this? I am using Delphi's Query component and I want to display columns from each table.

Answer:

First, the columns from each table must be included in the SELECT clause of the statement. Any columns not included will not be returned. However, if the columns list of a SELECT clause is an asterisk, all columns are returned.

Second, all tables in the join query must be listed in the FROM clause. How they are listed depends on the type of join used: inner, outer, full, or equi-join.

Third, a condition must be supplied by which any given row from one table is associated with some row from the other. Unless you supply this condition, you get what is known as a Cartesian join where every row from one table is joined with every row of the other. So if each table has 100 records, the result set will have 10,000 records (100*100). How the condition is provided varies with the type of join used. For equi-joins, the condition is in the WHERE clause. For all other type joins, the condition is in an ON section of the FROM clause.

Here is an example of an equi-join using the two sample Paradox tables Customer and Orders. From the Customer table, only the CustNo and Company columns are returned by the query. From the orders table, the OrderNo and AmountPaid columns are returned. These two tables have the CustNo column in common and so this is used for the join. (Join columns need not have the same name, only the same values between the two tables. In this case their having the same name is coincidental.):

SELECT C.CustNo, C.Company, O.OrderNo, O.AmountPaid
FROM "Customer.db" C, "Orders.db" O
WHERE (C.CustNo = O.CustNo)