mysql - SQL: Display results from two tables

Question

I have two tables:

Jobs

job_title         min_salary  job_id  max_salary 
Public Accountant 4322        1       8777
Assistant         4321        2       9877
President         5432        3       6766

Employees

emp_id  first_name  last_name  job_id  hire_date
100      Abc        Def        1       2020-12-09
101      Xyz        Efg        2       2020-10-05
102      Hjk        Lmn        3       2019-09-06

job_id is common in both tables. I want to display the first and last name and date of joining of the employees who are either Assistant or President...

I am trying to get the output using this:

SELECT first_name
     , last_name
     , hire_date 
  FROM employees
 UNION 
     ( SELECT job_title 
         FROM jobs 
        WHERE job_title = 'Assistant' 
           or job_title = 'President');

But I am doing something wrong when selecting the required columns...

Thanks

question from:https://stackoverflow.com/questions/65641771/sql-display-results-from-two-tables

Answer 1

You should to use JOIN:

SELECT first_name, last_name, hire_date 
FROM Employees
JOIN Jobs ON Employees.job_id = Jobs.job_id
WHERE job_title IN ('Assistant', 'President');

Here live SQL fiddle