[CRITERIA] Join without foreign key

a2ps · **Joined:** Mon Oct 01, 2007 6:23 am **Posts:** 20

Hi,

Id like to add result for one Table joining another, but the join columns is to a primaryKey for the second table.
Example:

Code:

User:
id
userInfo
loginId
firstName
lastName

Logins
id
loginId
firstName
lastName

the criteria query concern User class, if I get 2 rows, Id like to add to add lastName firstName for identical loginId

sql query could be like:

Code:

select u.id,u.userInfo, u.loginIf, l.firstName,l.lastName  FROM User u LEFT JOIN Logins l on u.loginId = l.loginId

a2ps · **Joined:** Mon Oct 01, 2007 6:23 am **Posts:** 20

any idea. .?

thanks!

a2ps · **Joined:** Mon Oct 01, 2007 6:23 am **Posts:** 20

any idea. .?

thanks!

Cameron McKenzie · **Posted:** Mon Jun 09, 2008 9:54 am

One idea is to have two results that dump their results into a set. Merge the set of the queries, and you have the results that you want.

Not the most elegant of solutions, but I've seen it done, and it works.

Here's a good tutorial on the Criteria API that you are using, if you're interested:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

a2ps · **Joined:** Mon Oct 01, 2007 6:23 am **Posts:** 20

thanks a lot Cameron ..

Cameron McKenzie · **Posted:** Tue Jun 10, 2008 9:28 pm

Well, it's not the most elegant solution, but it IS a solution, and sometimes, that's better than no solution at all.

Good luck!