Prashant Prashant - 1 month ago 4
MySQL Question

How to compare value from three table in mysql

I have three tables which are shown below:

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 |
+------------------+--------+


I want to get all
employee_name
which have same city as of the company they are working in. How can I do this.

Answer
SELECT DISTINCT e.employee_name
  FROM employee e,
       works w
       company c
 WHERE e.employee_name = w.employee_name
  AND w.company_name = c.company_name
  AND e.city = c.city;
Comments