Joins
The previous section explained how to use an
RWDBSelector to express the relational concepts of
restriction (the
WHERE clause in SQL) and
projection (the select list). In this section, we combine these abilities to produce a join. Actually, it's quite simple: we just mention columns from more than one table.
Example 10 – Performing a join
// Example Program 4 - Performing a join
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database()
"ODBC", // Access Module name
"odbc_dsn // server name
"user", // user name
"pwd", // password
"DEMO_DB" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase"); //1
RWDBTable videos = myDbase.table("videos"); //2
RWDBSelector select = myDbase.selector();
select << purchases["orderNum"] //3
<< purchases["supplierID"] << videos["title"]; //4
select.where(purchases["videoID"] == videos["ID"]); //5
RWDBReader rdr = select.reader(myConnection); //6
int purchaseOrderNumber;
int supplierID;
RWCString title;
while(rdr()) {
rdr >> purchaseOrderNumber >> supplierID >> title;
std::cout << purchaseOrderNumber << "\t"
<< title << "\t" << supplierID << std::endl;
}
return 0;
}
On //1 and //2, references to the database tables purchase and videos are obtained. On //3 and //4, the insertion operator << is used to specify the columns we wish to select: the orderNum and supplierID columns from the purchase table, and the title column from the videos table. As in previous examples, the tables to select FROM can be deduced from the column references.
On
//5, an expression that resolves to an
RWDBCriterion is specified. In it, we are restricting the resulting table to rows in which the
videoID column in the
purchase table is equal to the
ID column in the
videos table.
Beginning on
//6, an
RWDBReader is used to read the selected table. Despite the fact that this table cannot be associated with any particular physical storage, since it is generated from the given expression, the mechanism for reading it is the same as for any other table.
