CODEWITHSUNDEEP
mysqljoinunionmultiple-tables
owf
owf

Reputation: 253

Select all data from multiple table ON same field value

This is my database tables look like :

regular_employee :

+----+---------------+-----------------+-----------+----------------+
| id | employee_name | employee_job    | join_date | permanent_date |
+----+---------------+-----------------+-----------+----------------+
|  1 | ZELDA         | ACCOUNTANT      | 2020/02   | 2020/03        |
|  2 | YUGO          | QA              | 2020/02   | 2020/04        |
|  3 | XAVIER        | GENERAL MANAGER | 2020/02   | 2020/05        |
|  4 | WAVY          | FACTORY MANAGER | 2020/01   | 2020/02        |
+----+---------------+-----------------+-----------+----------------+

contract_employee :

+----+---------------+--------------+-----------+
| id | employee_name | employee_job | join_date |
+----+---------------+--------------+-----------+
|  1 | ANTONIO       | ACCOUNTANT   | 2020/01   |
|  2 | BAGGIO        | ENGINEER     | 2020/02   |
|  3 | CHARLES       | QA           | 2020/02   |
|  4 | DAVID         | QA           | 2020/02   |
+----+---------------+--------------+-----------+

My Goal : Select all employee_job who joined the company on 2020/02 and distinct / group them by employee_job

What i've tried :

SELECT re.employee_job,ce.employee_job 
FROM regular_employee AS re,contract_employee AS ce 
WHERE re.join_date = '2020/02' AND ce.join_date = '2020/02' 
GROUP BY re.employee_job,ce.employee_job

The result was :

+-----------------+--------------+
| employee_job    | employee_job |
+-----------------+--------------+
| ACCOUNTANT      | ENGINEER     |
| ACCOUNTANT      | QA           |
| GENERAL MANAGER | ENGINEER     |
| GENERAL MANAGER | QA           |
| QA              | ENGINEER     |
| QA              | QA           |
+-----------------+--------------+

What i was expecting :

+-----------------+
| employee_job    |
+-----------------+
| ACCOUNTANT      |
| ENGINEER        |
| GENERAL MANAGER |
| QA              |
+-----------------+

How to do this query ?

Upvotes: 1

Views: 697

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

A union query might make more sense here:

SELECT employee_job FROM regular_employee WHERE join_date = '2020/02'
UNION
SELECT employee_job FROM contract_employee WHERE join_date = '2020/02';

UNION by default will remove duplicate jobs which might appear in one/both of the two tables.

Upvotes: 2

Related Questions