MySQL JOIN condition if value in column is not null

Question

Query

SELECT employee.emp_number as employee_id,
employee.termination_id,
employee.firstname as user_name,
termination.termination_date 
FROM employee
JOIN user ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id 
  AND termination.termination_date > '2016-01-01') 
WHERE ohrm_user.created_by = '31' 
GROUP BY employee.emp_number

What I am trying

In the employee table by default termination_id is set NULL. If employee is terminated then the id from termination table is set as value. I want to get all employees who are not at all terminated or terminated before a certain date.

The above query gets all results including results with termination_date less than 2016-01-01. Is it possible to modify the query so that it omits the results with termination_date less than 2016-01-01? or do I have to execute 2 different queries and combine the result to achieve this.

Note to avoid confusion: I want employees who are still working (not terminated) AND not terminated before 2016-01-01

use this termination.termination_date > '2016-01-01' in where clause — 1000111
– 1000111, Commented Jan 27, 2016 at 6:50
you mean you want employees who are still working (did not terminated) OR terminated before 2016-01-01? — Gaurav Lad
– Gaurav Lad, Commented Jan 27, 2016 at 6:56
@SubrataDey It returns only terminated employees whose termination date >2016-01-01. Its doesn't return employees who are not terminated — Avinash
– Avinash, Commented Jan 27, 2016 at 6:57
@GauravLad I want employees who are still working (not terminated) AND terminated after 2016-01-01 — Avinash
– Avinash, Commented Jan 27, 2016 at 6:58
Try WHERE ohrm_user.created_by = '31' AND (employee.termination_id IS NULL OR termination.termination_date >= '2016-01-01') — Gaurav Lad
– Gaurav Lad, Commented Jan 27, 2016 at 7:04

Zafar Malik · Accepted Answer · 2016-01-27 07:01:45Z

1

try below query-

SELECT employee.emp_number AS employee_id,
employee.termination_id,
employee.firstname AS user_name,
termination.termination_date 
FROM employee
JOIN USER ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id ) 
WHERE ohrm_user.created_by = '31' 
AND (employee.termination_id IS NULL OR termination.termination_date >= '2016-01-01') 
GROUP BY employee.emp_number

answered Jan 27, 2016 at 7:01

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

abhinav · Accepted Answer · 2016-01-27 06:51:24Z

1

Move the

AND termination.termination_date > '2016-01-01'

from present position to after

WHERE ohrm_user.created_by = '31'

That should do the trick

answered Jan 27, 2016 at 6:51

abhinav

214 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-01-27 07:02:40Z

You can try moving the check on the termination_date to your WHERE clause:

SELECT employee.emp_number as employee_id, employee.termination_id,
    employee.firstname as user_name, termination.termination_date 
FROM employee
JOIN user
    ON user.emp_number = employee.emp_number 
JOIN reportto
    ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination
    ON termination.id = employee.termination_id 
WHERE (termination.termination_date IS NULL OR termination.termination_date > '2016-01-01')
    AND ohrm_user.created_by = '31'
GROUP BY employee.emp_number

A comment about the WHERE clause:

WHERE (termination.termination_date IS NULL OR termination.termination_date > '2016-01-01')

This will first check if termination.termination_date be NULL, which would indicate an employee record which did not match the termination table, implying he is still working. Second, if termination.termination_date be not NULL, then it checks that his date of termination occurred later than 2016-01-01.

Collectives™ on Stack Overflow

MySQL JOIN condition if value in column is not null

Query

What I am trying

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Query

What I am trying

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related