hi - i am having hard time translating the following regular sql..

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%'

my initial attempt to word it like follows applies the organization 'filter' to end result so it does not return all records from product as a left outer join should..

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

any idea is appreciated, thanks.

hmm, if opl.organization.id is not nullable you could try to add an or to your statement: where p.name like 'ms%' and (opl.organization.id = 1 or opl.organization.id is null)

Otherwise use the "with" keyword to supply extra join conditions: left outer join p.organizationPrivilegeList opl with opl.organization.id = 1

afaik "with" is not a valid keyword with ejb3 jpa but "or opl.organization.id is null" did the trick.. thanks.