In my scenario i have an Employee Table and a Dept Table. One Dept has many Employees and 1 Employee can belong to only one dept.
I have mapped the foreign keys correctly. Employee table has all employees and corresponding foreign key association to depts too, barring an Employee called "noDeptEmployee" who doesnot have a Dept for it.
Now i wanted employees and corresponding dept data for employees who have or not have an associated Dept for them. for the below query
SELECT t0.DEPT_ID, t0.DEPT_NAME, t2.NAME FROM EMPLOYEE t1
LEFT OUTER JOIN DEPT t0 ON (t0.DEPT_ID = t1.DEPT_DEPT_ID)
LEFT OUTER JOIN EMPLOYEE t2 ON (t2.DEPT_DEPT_ID = t0.DEPT_ID)
it didnt return me the "noDeptEmployee". I believed it should have.
The query was generated by JPA provider "Eclipse Link". JPA query is "SELECT d from Employee e LEFT JOIN e.dept d LEFT JOIN FETCH d.emps"
I know there are simpler queries to do what i am trying to acheive but trying to test my Sql skills here,
1) i cant understand why noDeptEmployee was not returned to me(All the other employees who have depts for them were returned) on the above query. I believe that Left Outer Joins should be serially processed? i.e First Left Outer Join first and then the result passed to Second Left Outer Join..Any pointers?
2) How come when only dept was needed in the select statement, the JPA changed it to Employee Name too?
1. In my experience autogenerated SQL is never a good idea unless you truly don't know any SQL at all. It is rarely the most efficient or simplest way to process a DBMS request and often doesn't even do it right. In this case, I don't have the slightest idea why it generated what it did.
2. I'm not following why you have the Employee table joined to Dept, joined back to Employee again (t2). My understanding is that you want to return all employee records regardless of whether they are assigned to a Dept or not (thereby getting the record with no dept assigned)? This can be achieved by simply selecting whatever name and dept info you need
LEFT OUTER JOIN Department
Of course, using your own table names, but you get the idea.