HQL: get a list where a join contains nulls

Question

I have three objects.

public class ParentClass
{
public virtual Guid ParentClassId { get; set; }
public virtual IList<Child> Children { get; set; }
}

public class Child
{
public virtual Guid ChildId { get; set; }
public virtual ParentClass Parent { get; set; }
}

public class Record
{
public virtual Guid RecordId { get; set; }
public virtual Child Child { get; set; }
}

ParentClass and a collection of Child is added into the database by an outside service. A service will run that will occasionally look for any of Child that need to be processed and bring back a list of ParentClass, as each Child in ParentClass should be processed together.

I want to write a HQL query to select the distinct parent of all Children where no Records's have a foreign key to them. Currently, I have this:

SELECT DISTINCT c.Parent FROM Child c LEFT JOIN Record r WHERE r is null

But I get an error saying a Path is expected. Any ideas?

Answer 1

You could try a subquery, eg:

SELECT DISTINCT c.Parent FROM Child c WHERE NOT EXISTS(FROM Record r WHERE r.Child = c)