title: MySQL link two tables together implicitly
body: <p>Suppose we have two tables</p>
  
  <p>A table called <strong>people</strong> with people linked to a bank account balances</p>
  
  <pre><code>| id | name | account_id |
  --------------------------
  | 1  | bob  | 11         |
  --------------------------
  | 2  | sam  | 22
  </code></pre>
  
  <p>A table called <strong>accounts</strong> with bank account balances</p>
  
  <pre><code>| id | value |
  --------------
  | 11 | 200   |
  --------------
  | 22 | 500   |
  </code></pre>
  
  <p>In order to link the two tables you can do</p>
  
  <pre><code>SELECT a.value as account_balance 
  FROM people p 
  WHERE p.name="bob" 
  LEFT JOIN accounts a ON p.account_id = a.id`
  </code></pre>
  
  <p>This would return</p>
  
  <pre><code>id =&gt; 1
  name =&gt; bob
  account_balance =&gt; 200
  </code></pre>
  
  <p>That's cool - but I am wondering if there is a more implicit way to do this via SQL linkage (foreign keys or otherwise). Can we in MySQL add links in some other way so that when we do a SELECT, it already knows to return <strong>value</strong> instead of **account_id **?</p>
  
  <p>I'm asking this because I am creating a system where my users can create lookup tables and link them to other tables - but it must be do-able without any programming. The only other way I can think of is to set the name of <strong>account_id</strong> for example to <strong>accounts.value</strong> and treat that as a foreign key when doing a SELECT.</p>
  
  <p>I would have to get the column structure and analyze and then determine that there is a foreign key and then return the appropriate foreign column by looking at the column name.</p>