Self-Joins
Experienced users of SQL know there are times when you want to join a table with itself. To accomplish this with an
RWDBSelector, declare a second instance of the
RWDBTable of interest. (This example is derived from
A Guide to the SQL Standard by C.J. Date.)
RWDBTable first = myDbase.table("supplier"); //1
RWDBTable second = myDbase.table("supplier"); //2
RWDBSelector select = myDbase.selector();
select << first["ID"] << second["ID"];
select.where(first["city"] == second["city"]); //3
From the
supplier table, the example selects pairs of
IDs representing suppliers in the same city. The
RWDBTable instances declared on
//1 and
//2 both refer to the same database table,
supplier. Recall from
Tables that these table references are inexpensive, since they don’t require any database access. The resulting selector represents an SQL code fragment of this type:
SELECT first.ID, second.ID
FROM supplier first, supplier second
WHERE first.city = second.city
NOTE: The SQL code here and in the section below is hypothetical, since actual SQL syntax varies from one database implementation to another. SourcePro DB handles these vendor-specific details from a single programming interface, so you don’t have to worry about them.
