Author: Tomas Rutkauskas
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)
|