i have the following ejbql that returns the wrong outer join; it does not return all records from product:

select distinct p, opl.privilege from Product p left outer join p.organizationPrivilegeList opl where p.name like 'ms%' and opl.organization.id = 1

correct behaving regular sql is as follows:

select * from vb_product p left outer join (select * from vb_product_privilege where organization_id = 1) as pp on p.id = pp.product_id where p.name like 'ms%'

difference being organization filter is applied before the outer join.

can anyone help me write the same meaning in ejbql? or is it possible to use subqueries with where clauses in joins? any sample?

thanks.