This discussion is archived
4 Replies Latest reply: Feb 11, 2010 8:29 PM by 843793 RSS

jpa joins 3 table

843793 Newbie
Currently Being Moderated

I have problem with my jsf 2.0 using JPA on startup. At first it was all fine and when I added up an additional
NamedQuery ("browseItemProCom") which is having a query of 3 different tables join together (inner join),
The first time my page loads it just wont show the results, but when I do refresh or open another browser the second time around, that's the time it shows my query.

Below are my query, the "browseItemProCom", it gives me trouble as mentioned above. I'm saying
it's giving me trouble because when I took it off, it works fine but I needed this "browseItemProCom" for another query.

The Query:

@NamedQuery(name="browseItemProCom", query="SELECT DISTINCT I,P,C FROM Items AS I
JOIN (I.props AS P JOIN I.compatibles AS C ON P.items_itemid = C.items_itemid)
ON I.itemid = C.items_itemid WHERE I.partnum = :partnum ORDER BY P.sequence")

the I.props is declared as
@OneToMany(cascade=CascadeType.ALL, mappedBy="items")
protected Collection<Properties> props = new ArrayList<Properties>();

and the I.compatibles is declared as
@OneToOne(cascade=CascadeType.ALL, mappedBy="items")
protected Compatibles compatibles;

Hope someone will give me some light on this.

regards, nelson