Join Tables
Sooner or later it's going to happen. You're going to need data from two tables at the same time. The select object has a join() method to allow you to fetch data from related tables. The object created by join() 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 join |
| required | false | boolean - indicates if data from the related table is required (true - inner join) or optional (false - left outer join) |
| column | n/a | the name of the target column in the joined table - this argument is optional if you use foreign key constraints, which also means loose couplinge == good thing |
| references | n/a | the name of the source column in the table being adjoined - this argument is optional if you use foreign key constraints (see above) |
Example:
<cfscript>
// get the datasource
ds = request.DataFaucet.getDatasource();
// get a select statement
stmt = ds.getSelect("product.*, pc.categoryname","tblProduct product");
// join the category table
stmt.join("productCategory pc",true,"productcategoryid","productcategoryid");
// or if you use foreign key constraints (DO IT!)
stmt.join("productCategory pc",true);
// get the results
stmt.execute();
</cfscript>