Subqueries
Because an
RWDBExpr instance can be constructed from an
RWDBSelector instance, it is easy to use selectors to express subqueries. For example:
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector select = myDbase.selector();
RWDBSelector avg = myDbase.selector();
avg << rwdbAvg(backup["onHand"]);
select << primary["title"];
select.where(primary["onHand"] >= avg);
This code fragment constructs a selector that is equivalent to the hypothetical SQL statement:
SELECT primary.title
FROM primary
WHERE primary.onHand >= (
SELECT AVG(onHand) FROM backup)
Notice that in the previous example no tables are referenced in both the outer query and the subquery. Special care is required when table references are shared between the outer query and the subquery. Consider the following example:
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector select = myDbase.selector();
RWDBSelector avg = myDbase.selector();
avg << rwdbAvg(backup["onHand"]);
avg.where(backup["ID"] == primary["ID"]);
select << primary["title"];
select.where(primary["onHand"] >= avg);
A reasonable interpretation of this code fragment could be expressed this way in SQL:
SELECT primary.title
FROM primary
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM primary, backup
WHERE backup.ID = primary.ID
)
However, we might also mean this:
SELECT primary.title
FROM primary
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM backup
WHERE backup.ID = primary.ID
)
In other words, we might want a correlated subquery. To get it, we mark the reference to the primary table as external in the subquery, since the table definition is supplied from the outer query. Here's how:
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector select = myDbase.selector();
RWDBSelector avg = myDbase.selector();
avg << rwdbAvg(backup["onHand"]);
avg.where(backup["ID"] == primary["ID"]);
avg.fromExtern(primary); //1
select << primary["title"];
select.where(primary["onHand"] >= avg);
By using the fromExtern() method in line //1, we specify that primary table is defined externally to avg. The definition is supplied elsewhere, and hence primary table should not be defined in the FROM clause of avg.
