CODEWITHSUNDEEP
mysql
Karan Ramesh
Karan Ramesh

Reputation: 3

Joining two tables in mysql with where clause condition

I have two tables, say Employees and Date of Joining.Now i want to join both tables based on the employee id but i want to display the complete list of employees irrespective of whether Joining date is been registered or not..Here are the two tables

Employee
John                        
Paul                        
George
Ringo

DOJ
21/05/1998
23/02/2006

EmpIds being 1,2,3,4 for John,Paul,George,Ringo respectively

I need both the tables to be joined and the values of George and Ringo can be null but my main concern is i should be able to filter the DOJ. Please look below.

SELECT e.empName, d.DOJ, e.empId
FROM employee AS e
LEFT JOIN doj AS d ON e.empId = d.empId
WHERE d.doj BETWEEN STR_TO_DATE('27/04/2000', '%d/%m/%Y')
  AND STR_TO_DATE('27/04/2014', '%d/%m/%Y'))

Now when i run this query i get all the records filtered by the dates but i need all the employee names not just the ones filtered by where clause(with doj as null which i can replace as "not available"). I wish there was a full outer join option in mysql.

Upvotes: 0

Views: 254

Answers (2)

CalixCaine
CalixCaine

Reputation: 33

i guess you did everything right in your query apart from your where condition. Based on your condition the result will be between the date of 27/04/2000 and 27/04/2014 so if the date value is "NULL" it won't be displayed.

Try the same query without that date condition you might get the result.

Upvotes: 0

Jens
Jens

Reputation: 69440

Try this untested query:

SELECT e.empName, d.DOJ, e.empId
  FROM employee AS e
   left JOIN doj AS d ON e.empId = d.empId
and d.doj between STR_TO_DATE('27/04/2000', '%d/%m/%Y')and STR_TO_DATE('27/04/2014', '%d/%m/%Y')and STR_TO_DATE('27/04/2014', '%d/%m/%Y'))

Upvotes: 0

Related Questions