title: Different result for @SQLResultSetMapping+Joins on multiple entities | JPA
body: <p>I am running a NativeQuery with JPA that gives different results compared to running the query in an sql tool. Probably I missunderstand s.th. within the concept of @SQLResultSetMapping.</p>
  
  <h1>--- Overview ---</h1>
  
  <p>I am mapping the result to two entities, so I am expecting to receive a List of Entity-Pairs. This works.</p>
  
  <p>When you look at the picture below, you'll see the result of the query in an sql tool, where ..</p>
  
  <ul>
  <li>.. the RED BOX maps to one entity</li>
  <li>.. the GREEN BOX maps to the second entity</li>
  </ul>
  
  <p>JPA should give me one of the native row as a pair of two entities.</p>
  
  <p><a href="https://i.stack.imgur.com/mxgXW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mxgXW.png" alt=""></a></p>
  
  <h2>Problem</h2>
  
  <p>This is where things go wrong. Yes, I will receive a list of pairs of both entities, but unlike in the picture the column "pp.id" does not iterate over all rows of the respective table (in the picture "5,6,7,..", from JPA "5,5,5,5,..").</p>
  
  <p>The column pp.id is a joined column, I guess that I missunderstand something within JPA when it comes to Joins + SQLResultSetMappings. It appears to me that the difference is that JPA is always joining THE SAME row from table 'propertyprofile' (more detailes below), unlike when the query is run in sql.</p>
  
  <p><a href="https://i.stack.imgur.com/XtBRZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XtBRZ.png" alt=""></a></p>
  
  <p>I hope that somebody takes pity on me and helps me out. :)</p>
  
  <h1>--- Details ---</h1>
  
  <h2>Query</h2>
  
  <p>I am basically trying to find out if every 'product', has defined a 'value' (table propertyvalue) for a predefined 'property' (table propertyprofile).</p>
  
  <p>The probably most relevant part is at the bottom, where "propertyprofile" is joined and "propertyvalue" is left-joined.</p>
  
  <pre><code>select sg.ID as 'sg.id', sg.Name as 'sg.name', ppcount.totalppcount as 'sg.totalppcount', ppcount.totalppothercount as 'sg.totalppothercount',
  p.ID as 'product.id', pp.id as 'pp.id', pp.Role as 'pp.role', pp.Name as 'pp.name',
  (case when pv.id is null then '0' else '1' end) as 'hasPropertyValue', pv.ID as 'pv.id', pv.StringValue, pv.IntervallMin, pv.IntervallMax
  from shoppingguide sg
  join
  (
      select sg.ID as 'sgid', count(*) as 'totalppcount', count(pp_other.ID) as 'totalppothercount' from propertyprofile pp_all
      left join propertyprofile pp_other on pp_other.id = pp_all.id AND pp_other.Role = '0'
      join shoppingguide sg on pp_all.ShoppingGuideID = sg.ID
      join shopifyshop ss on sg.ShopifyShopID = ss.ID
      where
      pp_all.ShoppingGuideID = sg.ID AND
      ss.Name = :shopName
      GROUP BY pp_all.ShoppingGuideID
  ) ppcount on ppcount.sgid = sg.id
  join shopifyshop ss on sg.ShopifyShopID=ss.ID
  join product p on p.ShopifyShopID = ss.ID
  join propertyprofile pp on (pp.ShoppingGuideID = sg.id AND pp.Role = '0')
  left join propertyvalue pv on (pv.ProductID=p.ID and pv.PropertyProfileID = pp.id)
  where 
  ss.Name = :shopName
  order by sg.id asc, p.id asc, pp.id asc
  ;
  </code></pre>
  
  <h2>Tables</h2>
  
  <p>There are a lot of tables involved, but these are the most important ones to understand the query:</p>
  
  <ul>
  <li>product</li>
  <li>propertyprofile - a feature that all products have (e.g. height, price)</li>
  <li>propertyvalue - data for a specific feature; relates to propertyprofile (e.g. 5cm; $120)</li>
  </ul>
  
  <h2>SQLResultSetMapping</h2>
  
  <p>The mapping is done onto two entites: ProductDataFillSummary_ShoppingGuideInformation, ProductDataFillSummary_ProductInformation.</p>
  
  <pre><code>@SqlResultSetMapping(
          name = "ProductDataFillSummaryMapping",
          entities = {
              @EntityResult (
                      entityClass = ProductDataFillSummary_ShoppingGuideInformation.class,
                      fields = {
                          @FieldResult(name = "shoppingGuideId", column = "sg.id"),
                          @FieldResult(name = "shoppingGuideName", column = "sg.name"),
                          @FieldResult(name = "numberOfTotalPropertyProfiles", column = "sg.totalppcount"),
                          @FieldResult(name = "numberOfTotalPropertyProfilesOther", column = "sg.totalppothercount")
                      }),
              @EntityResult(
                      entityClass = ProductDataFillSummary_ProductInformation.class,
                      fields = {
                          @FieldResult(name = "productID", column = "product.id"),
                          @FieldResult(name = "propertyProfileId", column = "pp.id"),
                          @FieldResult(name = "propertyProfileRole", column = "pp.role"),
                          @FieldResult(name = "propertyValueId", column = "pv.id"),
                          @FieldResult(name = "hasPropertyValue", column = "hasPropertyValue")
                          }
                      )
          })
  </code></pre>