Hello,

i have a question, how do i get the following statement done with HQL:

SELECT ..... FROM Company company
LEFT JOIN loginUsers l ON company.loginUserID = l.loginUserID AND l.loginUserActive=1 OR l.loginUserID=12

the "ON" statement is my problem, where i could have OR / AND or NOT connected statements.

Hibernate creates its standard ON statement (i guess) which is something like

LEFT JOIN loginUsers l ON company.loginUserID = l.loginUserID

with no additional conditions....

How to ?


Greetings


tvh

doesnt help because it doesnt cover any joins with multiple conditions in the ON statement !!!

The way I see it, the l.loginUserActive=1 OR l.loginUserID = 12 should just be in the where clause. It's not part of the JOIN.


What you're stating sounds like you want to retrieve the loginUsers data if the l.loginUserActive=1 OR l.loginUserID=12 is matched even if there's not a company match.

hi there, thank you for the fast reply ! with my example i try to demonstrate what i want... additional conditions in the joins' ON statement and additionally to that a WHERE statement... the WHERE statement should not contain the conditions which are placed in the ON statement.

Sincerely

tvh

I realize that but do you get any added benefit to joining on fields that aren't part of the key linking the two tables together ? If not, there's no need for the additional ON statements. just use the where clause.

hi there, thank you for your ideas... below is the statement i try to get into hql to work without using the createSQLQuery statement workaround:

SELECT Companies.companyID, Companies.companyParentID, Companies.companyName, 1 AS userCompanyID
FROM Companies
LEFT OUTER JOIN HiddenUserCompanies HiddenUserCompanies ON Companies.companyID = HiddenUserCompanies.companyID AND HiddenUserCompanies.loginUserID = 94
WHERE (Companies.companyParentID=2) AND (HiddenUserCompanies.userCompanyID IS NULL)
ORDER BY Companies.companyName

"Companies" is a table containing companies in a tree-format, "HiddenUserCompanies" is a table containing companies specific users may not be able to view in the tree....

the left join gives me "null"-column values for "userCompanyID" for users having rights to view the tree....

as you stated, i have to probably rewrite my SQL statement to make it HQL compatible... but i dont think that its going to be solved to move the additional condition "AND HiddenUserCompanies.loginUserID = 94
" into the where clause....

sincerely


tvh

Use HB 3.1beta2 and the HQL "WITH" clause.

Hi there,

is the HQL "WITH" clause a brand brand new feature of hibernate 3.1 beta 2 ? is there already an api-documentation ?

tahnx for your suggestion so far...

tvh....did you get the solution for your problem b'coz even I am facing the same problem.