MySQL 别名查询用于另一个查询

Question

我的标题不是很好，但让我解释一下我的情况。我有一个 jobs 表。 jobs 表有 2 个指向 users 表的外键:sales_rep_id 和 account_manager_id。

然后我有另一个名为 contact_info 的表，它与 users 表具有一对一的关系。

jobs
-----
sales_rep_id
account_manager_id
...

users
-----
first_name
last_name

contact_info
-----
user_id
home_phone

如果我想查询每个工作的两个人的电话号码，我会执行以下操作:

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

我可以做些什么来用连接的数据创建一个临时表吗？执行此连接的最有效方法是什么？例如，如果我在 jobs 表中有 10 个外键到 users 表，而我需要所有 10 个的 phone_number 怎么办？

Answer 1

这应该是获取你需要的所有信息

SELECT j.*
 , u1.first_name as sales_rep_first_name
 , u1.last_name as sales_reps_last_name
 , u2.first_name as manager_first_name
 , u2.u1.last_name as manager_last_name
 , c1.home_phone as sales_rep_home_phone
 , c2.home_phone as manager_home_phone
FROM jobs as j
INNER JOIN contact_info as c1 ON j.sales_rep_id = c1.user_id
INNER JOIN user u1 ON u1.id= c1.user_id
INNER JOIN contact_info as c2 ON j.saccount_manager_id = c2.user_id
INNER JOIN user u2 ON u2.id= c2.user_id;