From: Farrukh Najmi <farrukh_at_wellfleetsoftware.com>
Date: Wed, 29 Apr 2009 09:46:58 -0400

Hi Guys,

I have the following JPA entity beans based on familiar Order/LineItem
example used in JPA spec:

public class Order {

    ...
    protected List<LineItem> lineItems;

    @OneToMany(targetEntity = LineItem.class, cascade = {
        CascadeType.ALL
    })
    @JoinColumn(name = "LINEITEM_ORDER_ID")
    public List<LineItem> getLineItems() {...}
}

public class LineItem {
    ...
}
   
I need a query that will find all LineItems that are not in an Order's
lineItem collection attribute. The following query does not seem to
work. It returns all LineItems and not just the orphan LineItems.

SELECT DISTINCT Object(orphanLineItem) FROM LineItem orphanLineItem,
Order o, IN (o.lineItems) inUseLineItems WHERE orphanLineItem NOT IN
inUseLineItems

Any suggestions would be greatly appreciated on how to fix above query
so it matches only those LineItems that are not in any Order's lineItems
collection attribute. Thanks.

-- 
Regards,
Farrukh
Web: http://www.wellfleetsoftware.com