aspose file tools*
The moose likes JDBC and the fly likes Cannot understand MySqlQuery Working. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cannot understand MySqlQuery Working." Watch "Cannot understand MySqlQuery Working." New topic
Author

Cannot understand MySqlQuery Working.

sandeeprajsingh tandon
Ranch Hand

Joined: Mar 06, 2009
Posts: 70
Hi,
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?

Thanks
Sandeep
Tim Batts
Greenhorn

Joined: Aug 09, 2010
Posts: 10
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

FROM Employee
LEFT OUTER JOIN Department
ON Employee.DeptID=Department.DeptID

Of course, using your own table names, but you get the idea.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cannot understand MySqlQuery Working.