Reference - Select options - join

<p>where: {offices where="country = 'USA'", order='officeCode'}{$officeCode},{/offices}</p>
<p>distinct: {offices distinct, fields='country', order='country'}{$country},{/offices}</p>
<p>distinctrow: {offices distinctrow, fields='country', order='country'}{$country},{/offices}</p>
<p>group: {payments fields='customerNumber, count(*) as cnt', group='customerNumber', having='cnt > 9', order='customerNumber'}{$customerNumber}={$cnt},{/payments}</p>
<p>rollup: {offices key='', fields='count(*) as cnt', group='country', rollup}{last@offices}{$cnt}{/last@offices}{/offices}</p>
<p>db: {sales db='employees', where="jobTitle = 'President'"}{$lastName}{/sales}</p>
<p>key: {offices key='city', where="country = 'USA'"}{$city},{/offices}</p>
<p>union: {offices where="officeCode = 1", union='offices'}{$officeCode},{/offices}</p>
<p>join: {employees join='offices on employees.officeCode = offices.officeCode', where="offices.city = 'Boston'", order='lastName'}{$lastName},{/employees}</p>
<p>htmlAttrJson: {select:offices htmlAttrJson='record', fields='officeCode', where="officeCode = 1"}</p>

<?php

  // A pager after a select that joins two tables sharing a column name - orders and
  // customers, both with customerNumber - counts its rows: the count ran the select list
  // as a derived table, which MySQL refuses with two columns of one name ("1060: Duplicate
  // column name 'customerNumber'"), and the page ended at the pager.

  $padSelect ['ordersPaged'] = [ 'db' => 'orders', 'key' => 'orderNumber' ];

?>

[{ordersPaged join='customers on customers.customerNumber = orders.customerNumber', where="customers.country = 'Norway'", order='orderNumber', page=1, rows=3}{$orderNumber} {$customerName}; {/ordersPaged}]
{pager 'ordersPaged', window=0}{$kind}:{$page} {/pager}

<?php

  $padSelect ['table1'] = [ 'key' => 'key', 'fields' => ['key' => 'key1', 'text' => 'text1'] ];
  $padSelect ['table2'] = [ 'key' => 'key', 'fields' => ['key' => 'key2', 'text' => 'text2'] ];
  $padSelect ['table3'] = [ 'key' => 'key', 'fields' => ['key' => 'key3', 'text' => 'text3'] ];
  $padSelect ['table4'] = [ 'key' => 'key', 'fields' => ['key' => 'key4', 'text' => 'text4'] ];

  $padSelect ['join']     = [ 'base' => 'table1', 'join' => 'table2' ];

  $padSelect ['inner']    = [ 'base' => 'table1', 'join' => [ 'inner'         => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural1'] = [ 'base' => 'table1', 'join' => [ 'natural'       => 'table2'                      ] ];
  $padSelect ['left']     = [ 'base' => 'table1', 'join' => [ 'left'          => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural2'] = [ 'base' => 'table1', 'join' => [ 'natural left'  => 'table2'                      ] ];
  $padSelect ['right']    = [ 'base' => 'table1', 'join' => [ 'right'         => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural3'] = [ 'base' => 'table1', 'join' => [ 'natural right' => 'table2'                      ] ];
  $padSelect ['cross']    = [ 'base' => 'table1', 'join' => [ 'cross'         => 'table2'                      ] ];

  $padSelect ['multi']    = [ 'base' => 'table1', 'join' => [ ['inner' => 'table2', 'key'=> 'table1.key'],
                                                              ['inner' => 'table3', 'key'=> 'table1.key'],
                                                              ['inner' => 'table4', 'key'=> 'table1.key'] ] ];
?>

<h1>Join demo</h1>

<h2>join</h2>

<table border=1>
  {join}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
    </tr>
  {/join}
</table>

<h2>natural</h2>

<table border=1>
  {natural1}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural1}
</table>

<h2>inner</h2>

<table border=1>
  {inner}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/inner}
</table>

<h2>left</h2>

<table border=1>
  {left}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/left}
</table>

<h2>natural left</h2>

<table border=1>
  {natural2}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural2}
</table>

<h2>right</h2>

<table border=1>
  {right}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/right}
</table>

<h2>natural right</h2>

<table border=1>
  {natural3}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural3}
</table>

<h2>cross</h2>

<table border=1>
  {cross}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/cross}
</table>

<h2>multi</h2>

<table border=1>
  {multi}
    <tr>
      <td>{$text3}</td>
      <td>{$text4}</td>
    </tr>
  {/multi}
</table>

<?php

  // Paging a declared select table: the page is cut once, by the SQL limit, so page 2 holds
  // the third and fourth rows; a row count in the declaration is a row count, not the
  // query's command word; and a joined table declared without fields joins, adding none.
  // Over the four-row staff table and the join tables of the demo database.

  $padSelect ['staffSel'] = [ 'db' => 'staff', 'key' => 'name', 'order' => 'name' ];
  $padSelect ['staffTwo'] = [ 'db' => 'staff', 'key' => 'name', 'order' => 'name', 'rows' => 2 ];

  $padSelect ['bare']     = [ 'db' => 'table2', 'key' => 'key' ];
  $padSelect ['joined']   = [ 'db' => 'table1', 'fields' => 'table1.text', 'order' => 'table1.key',
                              'join' => [ 'inner' => 'bare', 'key' => 'table1.key' ] ];

?>

page 1: [{staffSel page=1, rows=2}{$name} {/staffSel}]
page 2: [{staffSel page=2, rows=2}{$name} {/staffSel}]
declared rows: [{staffTwo}{$name} {/staffTwo}]
join without fields: [{joined}{$text} {/joined}]