mysql - mysql中如何比较三个表的值

Question

我有如下所示的三个表:

Employee
+---------------+-------------+--------+
| employee_name | street      | city   |
+---------------+-------------+--------+
| abhishek      | viman nagar | pune   |
| neha          | lohegaon    | pune   |
| prasahnt      | viman nagar | pune   |
| rahul         | viman nagar | pune   |
| rahul singh   | lohegaon    | mumbai |
+---------------+-------------+--------+

Works
+---------------+------------------+--------+
| employee_name | company_name     | salary |
+---------------+------------------+--------+
| prasahnt      | First Bank Corp  |  90000 |
| rahul         | First Bank Corp  |  85000 |
| abhishek      | First Bank Corp  |  95000 |
| rahul singh   | Second Bank Corp |  45000 |
| neha          | Second Bank Corp |  65000 |
+---------------+------------------+--------+

Company
+------------------+--------+
| company_name     | city   |
+------------------+--------+
| First Bank Corp  | pune   |
| Second Bank Corp | mumbai |
+------------------+--------+

我想获取所有与他们工作的公司所在城市相同的employee_name。我该怎么做。

Answer 1

应该可以使用 JOIN 和 WHERE 来做到这一点，如下所示:

SELECT * 
FROM Employee e
JOIN Works w ON w.employee_name = e.employee_name
JOIN Company c ON c.company_name = w.company_name
WHERE c.city = e.city