MySQL alias a query for use in another query
<p>My title isn't great, but let me explain my situation.  I have a <code>jobs</code> table.  The jobs table has 2 foreign keys to the <code>users</code> table: <code>sales_rep_id</code> and <code>account_manager_id</code>.</p>

<p>Then I have another table called <code>contact_info</code> with a one to one relationship to the users table.</p>

<pre><code>jobs
-----
sales_rep_id
account_manager_id
...

users
-----
first_name
last_name

contact_info
-----
user_id
home_phone
</code></pre>

<p>If I want to do a query where I get the phone number for both people on every job I would do the following:</p>

<pre><code>SELECT reps.home_phone as reps_home, account_managers.home_phone as a_m_home FROM jobs
JOIN 
  (SELECT * FROM users 
   JOIN contact_info 
   ON users.id = contact_info.user_id) reps
ON reps.id = jobs.sales_rep_id
JOIN 
  (SELECT * FROM users 
   JOIN contact_info 
   ON users.id = contact_info.user_id) account_managers
ON account_managers.id = jobs.account_manager_id
</code></pre>

<p>Is there anything I can do to create a temporary table with the joined data?  What is the most efficient way to do this join? For example, what if I had 10 foreign keys in the jobs table to the users table, and I needed the phone_number for all 10?</p>