Unions
The select object has a union() method to allow you to create SQL union statements. The object created by union() is simply another Select object with all the same properties and methods.
The most important arguments to the join method are these:
| Property | Default | Description |
|---|---|---|
| table | n/a | The related table to union |
| columns | n/a | a list of columns to return in the union table - remember that columns in each union statement must match |
| all | n/a | boolean - if true overrides the default union behavior of removing duplicates and allows you to see any duplicted entries in the results |
Example:
<cfscript>
// get the datasource
ds = request.DataFaucet.getDatasource();
// get a select statement
stmt = ds.getSelect("productname,productprice","tblProduct");
// union the archived products table
stmt.union("tblProductArchive","productname,productprice");
// get the results
stmt.execute();
</cfscript>