SQL: Display results from two tables

user_14176886 0
Viewed 41

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

mysql-error-1064 join mysql sql
2 Answers

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

user_33993560

You need to change the query like this:

SELECT *
  FROM tutorial.crunchbase_investments_part1

 UNION

 SELECT *
   FROM tutorial.crunchbase_investments_part2

Source

user_149235830
Related