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