I have the following tables.

Site
SiteID (PK)
...
LevelID

LevelPriceRangeList
LevelID (PK)
PriceRangeID (PK)

PriceRange
PriceRangeID (PK)
Start
End
...

I have EJB entities for the Site table and the PriceRange table. In the Site table I have a JoinTable relationships that gives me a Set<PriceRange>.

It all works great, except I want a query that will give me a PriceRange with a given siteId and an amount between Start and End. The problem is I can't figure out how to do it with the EJB QL because of the Set.

Thanks,
Eric

I figured it out with this query.

String QUERY = "Select pr from Site as s, PriceRange as pr where pr in elements(s.ranges) and s.siteId=:siteId and " + cost.toString() + "between pr.start and pr.end";

return (PriceRange) pbPM.getEntityManager().createQuery(QUERY).setParameter("siteId", Integer.valueOf(siteId.trim())).getSingleResult();