Outer Join Constructs In the WHERE Clause (ANSI-Noncompliant)
If your database does not support SQL 92 syntax, you can generate an outer join construct as part of the WHERE clause. Use the following procedure:
1. Create an RWDBCriterion outer join condition using the leftOuterJoin() or rightOuterJoin() methods on either the RWDBColumn or the RWDBExpr class.
2. Specify the criterion, combined with any other criterions, in the WHERE clause.
The following code shows how to implement the procedure:
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
An Outer Join in the WHERE Clause
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"ctl<ver>12d.dll", // Access Module name
"sybserver", // server name
"tutor", // user name
"passwd", // password
"db" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase");
RWDBTable videos = myDbase.table("videos");
RWDBSelector selector = myDbase.selector();
selector << purchases["orderNum"]
<< purchases["supplierID"] << videos["title"];
selector.where(purchases["videoID"].leftOuterJoin(videos["ID"])); //1
RWDBReader rdr = selector.reader(myConnection);
int purchaseOrderNumber, supplierID;
RWCString title;
while (rdr()) {
rdr >> purchaseOrderNumber >> supplierID >> title;
std::cout << purchaseOrderNumber << "\t"
<< supplierID << "\t" << title
<< std::endl;
}
return 0;
}
On //1, a left outer join expression is constructed and set in the WHERE clause. The criterion says that the two tables purchase and videos should be joined such that the videoID column in purchase should match the ID column in videos. In this case, the tables list for the FROM clause is implicitly derived from the selection list. The rest of the code is similar to Example 10, Performing a join.
Please note that different databases may impose restrictions on generating outer join constructs in the WHERE clause:
Some databases do not support outer joins in which one table is outer to more than one other table; other databases give unexpected results under these circumstances. All other joins besides outer joins should work consistently across all databases.Please see the appropriate DB Access Module guide for more details.
