2 Replies Latest reply on Jul 24, 2007 3:04 AM by kavi arasu

    database union in ejb3

    kavi arasu Newbie

      Hi i want to write a query as i had given below in ejb3
      select field_1,field_2 from table1 t1,table2 t2 where t1.pk=t2.fk
      union
      select field_1,field_2 from table1 t1,table3 t3 where t1.pk=t3.fk

      where
      Table 1 is the entity with PK as a primary key.
      Table 2 is using PK as a foreign key.
      Table 3 is using PK as a foreign key.
      Tables 2 and 3 are only related through that foreign key and contain dissimilar data that relates to Table 1.
      The relationship between table 1 & 2 and 1 & 3 are both one to many.

      can any onr suggest me how to achive this

      my entity bean is


      @Entity
      @Table(name = "TBL_REVIEWS")
      
      
      @SecondaryTables({
       @SecondaryTable(name="TBL_USER_REVIEWS",
       pkJoinColumns={@PrimaryKeyJoinColumn (name="REVIEW_ENTRY_ID")}),
       @SecondaryTable(name="TBL_COMMENTS",
       pkJoinColumns={@PrimaryKeyJoinColumn (name="REVIEW_ENTRY_ID")})
      })



      can any one help me soon
      thanks
      regards
      Kaviarasu