w3resource

MySQL Subquery Exercises: Find the name of the employees who have a manager and worked in a USA based department

MySQL Subquery: Exercise-3 with Solution

Write a MySQL query to find the name (first_name, last_name) of the employees who have a manager and worked in a USA based department.

Sample table: employees


Sample table : departments


Sample table : locations


Code:

-- Selecting the first name and last name of employees 
SELECT first_name, last_name 
-- Selecting data from the employees table
FROM employees 
-- Filtering the result set to include only employees whose manager_id is in the set of employee_ids 
-- where the department_id is in the set of department_ids associated with locations in the US
WHERE manager_id in 
    (SELECT employee_id 
    -- Subquery to select employee_ids from the employees table
    FROM employees 
    -- Filtering the employee_ids to include only those associated with departments 
    -- where the location_id is in the set of location_ids associated with countries having country_id 'US'
    WHERE department_id 
    IN 
        (SELECT department_id 
        -- Subquery to select department_ids from the departments table
        FROM departments 
        -- Filtering the department_ids to include only those associated with locations 
        -- where the country_id is 'US'
        WHERE location_id 
        IN 
            (SELECT location_id 
            -- Subquery to select location_ids from the locations table
            FROM locations 
            -- Filtering the location_ids to include only those associated with countries 
            -- having country_id 'US'
            WHERE country_id='US')
        )
    );

Explanation:

  • This MySQL code selects the first name and last name of employees from a table named "employees".
  • It filters the results to only include employees whose manager_id is in the set of employee_ids where the department_id is in the set of department_ids associated with locations in the US.
  • This is achieved by using nested subqueries:
    • The innermost subquery selects location_ids from the "locations" table where the country_id is 'US'.
    • The next subquery selects department_ids from the "departments" table where the location_id is in the set obtained from the inner subquery.
    • The subsequent subquery selects employee_ids from the "employees" table where the department_id is in the set obtained from the previous subquery.
    • Finally, the outermost query selects the first name and last name of employees where the manager_id is in the set obtained from the inner subquery.

MySQL Subquery Syntax:

operand comparison_operator
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators

=  >  <  >=  <=  <>  != 

and IN operator checks whether a value is within a set of values.

For example :

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot.

MySQL: Find the names of the employees who have a manager, works for a department based in United States

 

MySQL Code Editor:

Structure of 'hr' database :

hr database

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to find the name (first_name, last_name) of all employees who works in the IT department.
Next:Write a MySQL query to find the name (first_name, last_name) of the employees who are managers.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.