So my problem is how to transform MySql SQL with LEFT JOIN ON to HSQL ?

I work with MySql version that doesn't support subqueries, SQL looks like



-- select all records from jpt_user where jpt_user id is not in (select user_id from jpt_tw_user)

So, this is just how to make NOT IN subquery if your database doesn't support subqueries.

I need to write HSQL for this situation.

Please help and thanks.

Marko

what version of mysql? (i assume you can't upgrade...)

hql should be able to represent it as you showed it in the first part without the not in. alternatively, using criteria should not cause this either.

you should be able to represent it as

SELECT u.* FROM jpt_user u
LEFT JOIN jpt_tw_user tu ON u.id=tu.user_id and tu.user_id = NULL

your second query is doing a different query - not in will give you different results to = null - which are you using, and which is giving you the problem?

Sorry, I'm new here ... so maybe I wasn't clear enough :-)

No, I can't upgrade mysql :-(

So, this is query in MySQL



that replaces NOT IN subquery ...

What I have to do is to transform that query to HQL.

I hope it is clearer now :-)

Thanks.

Not sure what your class names are but another way is:

assuming that jpt_user is a mapped class and has a property for a class jpt_tw_user with that exact name:
and exists a property id in your class jpt_tw_user


Take a look at :

HQL

hm .. it is backwards :-)

I have two mapped classes JptUser and JptTwUser ... JptTwUser has a property for a class JptUser (many-to-one) ... so JptUser has Set of JptTwUser classes.

I tried something like "give me from JptUser where Set of JptTwUser classes is empty" ... but I didn't make it :-(

Marko.

I think like this assuming you mapped the set??:
I am assuming the set is mapped and has a name like
jptTwUsers



This should work (did not test) and return you a set of JptUser where the property id is not null.....

Ok??

Yes, thanks ... only change is that it should be IS NULL instead of IS NOT NULL ... but you helped me a lot and solved my problem ... thanks

just out of curiosity, surely this will ignore the left join and create an outer join since there's no join specified between the two fields?

No it will do a left join ..... You are not working with SQL but HQL you specify the join in ju.jptTwUsers Hibernate will resolve which fields to join from the mapping . Take a look at Associations and joins

good point, was in my sql mind :s