Dear All
This might be a silly question but I couldn't solve it by myself, I have got two table like schema below:

MEMBER(memberId, name, email)
ADDRESS(adressId, memberId, countryCode)

Relation
MEMBER(1)<---memberId--->(1)ADDRESS

I use subquery to get members who have countryCode like, 99, like this

SELECT member FROM MEMBER as member WHERE member.memberId IN(
SELECT address.memberId FROM ADDRESS as address WHERE address.countryCode=99);

I really don't know how to map this to HQL query without using sub-query because MySQL
4.0 still doesnot support sub-query.
Thanks

You can do a join - but in anycase, HQL will be converted approrpriately to multiple SQLs if subselects are not supported by the dialect. Just try it out and see what is generated.

Oh I C and finally I got it by following this thread

http://forum.hibernate.org/viewtopic.php?t=924769&highlight=subquery

and do something like this for my HQL:

select member from Member as member inner join member.arress as address where address.country = 99");

Thanks for bochi , emmanuel, gavin and MCWelch for finish that useful thread.
Cheers
Cool