4 Replies Latest reply: Feb 11, 2010 10:29 PM by 843793 RSS

    jpa joins 3 table

    843793
      Hi!

      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