<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="multiple-tables">
      </a>
      5.3.4.9 Using More Than one Table
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045324937088">
  </a>
  <p>
   The
   <code class="literal">
    pet
   </code>
   table keeps track of which pets you
          have. If you want to record other information about them, such
          as events in their lives like visits to the vet or when
          litters are born, you need another table. What should this
          table look like? It needs to contain the following
          information:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The pet name so that you know which animal each event
              pertains to.
     </p>
    </li>
    <li class="listitem">
     <p>
      A date so that you know when the event occurred.
     </p>
    </li>
    <li class="listitem">
     <p>
      A field to describe the event.
     </p>
    </li>
    <li class="listitem">
     <p>
      An event type field, if you want to be able to categorize
              events.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Given these considerations, the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
          TABLE
    </code>
   </a>
   statement for the
   <code class="literal">
    event
   </code>
   table might look like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54444146"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">event</span> <span class="token punctuation">(</span><span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token datatype">date</span> <span class="token datatype">DATE</span><span class="token punctuation">,</span>
       <span class="token keyword">type</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span> remark <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As with the
   <code class="literal">
    pet
   </code>
   table, it is easiest to
          load the initial records by creating a tab-delimited text file
          containing the following information.
  </p>
  <div class="informaltable">
   <table summary="pet record data that appears in a tab delimited text file, as described in the preceding text.">
    <colgroup>
     <col style="width: 15%"/>
     <col style="width: 15%"/>
     <col style="width: 15%"/>
     <col style="width: 35%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       name
      </th>
      <th scope="col">
       date
      </th>
      <th scope="col">
       type
      </th>
      <th scope="col">
       remark
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       Fluffy
      </th>
      <td>
       1995-05-15
      </td>
      <td>
       litter
      </td>
      <td>
       4 kittens, 3 female, 1 male
      </td>
     </tr>
     <tr>
      <th scope="row">
       Buffy
      </th>
      <td>
       1993-06-23
      </td>
      <td>
       litter
      </td>
      <td>
       5 puppies, 2 female, 3 male
      </td>
     </tr>
     <tr>
      <th scope="row">
       Buffy
      </th>
      <td>
       1994-06-19
      </td>
      <td>
       litter
      </td>
      <td>
       3 puppies, 3 female
      </td>
     </tr>
     <tr>
      <th scope="row">
       Chirpy
      </th>
      <td>
       1999-03-21
      </td>
      <td>
       vet
      </td>
      <td>
       needed beak straightened
      </td>
     </tr>
     <tr>
      <th scope="row">
       Slim
      </th>
      <td>
       1997-08-03
      </td>
      <td>
       vet
      </td>
      <td>
       broken rib
      </td>
     </tr>
     <tr>
      <th scope="row">
       Bowser
      </th>
      <td>
       1991-10-12
      </td>
      <td>
       kennel
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Fang
      </th>
      <td>
       1991-10-12
      </td>
      <td>
       kennel
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Fang
      </th>
      <td>
       1998-08-28
      </td>
      <td>
       birthday
      </td>
      <td>
       Gave him a new chew toy
      </td>
     </tr>
     <tr>
      <th scope="row">
       Claws
      </th>
      <td>
       1998-03-17
      </td>
      <td>
       birthday
      </td>
      <td>
       Gave him a new flea collar
      </td>
     </tr>
     <tr>
      <th scope="row">
       Whistler
      </th>
      <td>
       1998-12-09
      </td>
      <td>
       birthday
      </td>
      <td>
       First birthday
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <div class="informaltable">
   <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 519px;">
    <thead>
     <tr>
      <th scope="col" style="width: 97.0625px;">
       name
      </th>
      <th scope="col" style="width: 97.0625px;">
       date
      </th>
      <th scope="col" style="width: 97.0625px;">
       type
      </th>
      <th scope="col" style="width: 226.516px;">
       remark
      </th>
     </tr>
    </thead>
   </table>
  </div>
  <p>
   Load the records like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75601629"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">DATA</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'event.txt'</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> <span class="token keyword">event</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Based on what you have learned from the queries that you have
          run on the
   <code class="literal">
    pet
   </code>
   table, you should be able to
          perform retrievals on the records in the
   <code class="literal">
    event
   </code>
   table; the principles are the same.
          But when is the
   <code class="literal">
    event
   </code>
   table by itself
          insufficient to answer questions you might ask?
  </p>
  <p>
   Suppose that you want to find out the ages at which each pet
          had its litters. We saw earlier how to calculate ages from two
          dates. The litter date of the mother is in the
   <code class="literal">
    event
   </code>
   table, but to calculate her age on
          that date you need her birth date, which is stored in the
   <code class="literal">
    pet
   </code>
   table. This means the query requires
          both tables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa77338655"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> pet<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
       <span class="token function">TIMESTAMPDIFF</span><span class="token punctuation">(</span><span class="token datatype">YEAR</span><span class="token punctuation">,</span>birth<span class="token punctuation">,</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> age<span class="token punctuation">,</span>
       remark
       <span class="token keyword">FROM</span> pet <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> <span class="token keyword">event</span>
         <span class="token keyword">ON</span> pet<span class="token punctuation">.</span><span class="token keyword">name</span> <span class="token operator">=</span> <span class="token keyword">event</span><span class="token punctuation">.</span><span class="token keyword">name</span>
       <span class="token keyword">WHERE</span> <span class="token keyword">event</span><span class="token punctuation">.</span><span class="token keyword">type</span> <span class="token operator">=</span> <span class="token string">'litter'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name   <span class="token punctuation">|</span> age  <span class="token punctuation">|</span> remark                      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Fluffy <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> 4 kittens, 3 female, 1 male <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Buffy  <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span> 5 puppies, 2 female, 3 male <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Buffy  <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span> 3 puppies, 3 female         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   There are several things to note about this query:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       FROM
      </code>
      clause joins two tables
              because the query needs to pull information from both of
              them.
     </p>
    </li>
    <li class="listitem">
     <p>
      When combining (joining) information from multiple tables,
              you need to specify how records in one table can be
              matched to records in the other. This is easy because they
              both have a
      <code class="literal">
       name
      </code>
      column. The query uses
              an
      <code class="literal">
       ON
      </code>
      clause to match up records in the
              two tables based on the
      <code class="literal">
       name
      </code>
      values.
     </p>
     <p>
      The query uses an
      <code class="literal">
       INNER JOIN
      </code>
      to combine
              the tables. An
      <code class="literal">
       INNER JOIN
      </code>
      permits rows
              from either table to appear in the result if and only if
              both tables meet the conditions specified in the
      <code class="option">
       ON
      </code>
      clause. In this example, the
      <code class="literal">
       ON
      </code>
      clause specifies that the
      <code class="literal">
       name
      </code>
      column in the
      <code class="literal">
       pet
      </code>
      table must match the
      <code class="literal">
       name
      </code>
      column in the
      <code class="literal">
       event
      </code>
      table. If a name appears in one
              table but not the other, the row does not appear in the
              result because the condition in the
      <code class="literal">
       ON
      </code>
      clause fails.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because the
      <code class="literal">
       name
      </code>
      column occurs in both
              tables, you must be specific about which table you mean
              when referring to the column. This is done by prepending
              the table name to the column name.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You need not have two different tables to perform a join.
          Sometimes it is useful to join a table to itself, if you want
          to compare records in a table to other records in that same
          table. For example, to find breeding pairs among your pets,
          you can join the
   <code class="literal">
    pet
   </code>
   table with itself to
          produce candidate pairs of live males and females of like
          species:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55842120"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> p1<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> p1<span class="token punctuation">.</span>sex<span class="token punctuation">,</span> p2<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> p2<span class="token punctuation">.</span>sex<span class="token punctuation">,</span> p1<span class="token punctuation">.</span>species
       <span class="token keyword">FROM</span> pet <span class="token keyword">AS</span> p1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> pet <span class="token keyword">AS</span> p2
         <span class="token keyword">ON</span> p1<span class="token punctuation">.</span>species <span class="token operator">=</span> p2<span class="token punctuation">.</span>species
         <span class="token operator">AND</span> p1<span class="token punctuation">.</span>sex <span class="token operator">=</span> <span class="token string">'f'</span> <span class="token operator">AND</span> p1<span class="token punctuation">.</span>death <span class="token operator">IS</span> <span class="token boolean">NULL</span>
         <span class="token operator">AND</span> p2<span class="token punctuation">.</span>sex <span class="token operator">=</span> <span class="token string">'m'</span> <span class="token operator">AND</span> p2<span class="token punctuation">.</span>death <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name   <span class="token punctuation">|</span> sex  <span class="token punctuation">|</span> name  <span class="token punctuation">|</span> sex  <span class="token punctuation">|</span> species <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Fluffy <span class="token punctuation">|</span> f    <span class="token punctuation">|</span> Claws <span class="token punctuation">|</span> m    <span class="token punctuation">|</span> cat     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Buffy  <span class="token punctuation">|</span> f    <span class="token punctuation">|</span> Fang  <span class="token punctuation">|</span> m    <span class="token punctuation">|</span> dog     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   In this query, we specify aliases for the table name to refer
          to the columns and keep straight which instance of the table
          each column reference is associated with.
  </p>
 </div>
 <br/>
</div>
