'Inner join when it should be left join in JPA

I'm using JpaRepository and I'm creating a @Query in my repository:

this is my query:

@Query( "SELECT SUM(p.prima) as prima, p.producto as producto, p.tipoProducto as tipoProducto, p.compania as compania, p.cliente as cliente, p.vendedor as vendedor " +
        "FROM Poliza p " +
        "JOIN Producto pr       ON p.producto=pr " +
        "JOIN TipoProducto tp   ON p.tipoProducto=tp " +
        "JOIN Compania c        ON p.compania=c " +
        "JOIN Cliente cl        ON p.cliente=cl " +
        "LEFT JOIN Vendedor v   ON p.vendedor=v " +
        "WHERE p.comienzo >=?1 " +
        "AND p.comienzo <= ?2 " +
        "GROUP BY p.producto")

and I realize that I only get the rows where "Vendedor" is present. I used the spring.jpa.show-sql=true property to check what was going on and I realize that the query is creating an inner join for each property in the SELECT STATEMENT

inner join producto producto1_ on (poliza0_.producto=producto1_.id) 
inner join tipo_producto tipoproduc2_ on (poliza0_.tipo_producto=tipoproduc2_.id)
inner join compania compania3_ on (poliza0_.compania=compania3_.id) 
inner join cliente cliente4_ on (poliza0_.cliente=cliente4_.id) 
inner join vendedor vendedor5_ on (poliza0_.vendedor=vendedor5_.id) 

join producto producto6_ on poliza0_.producto=producto6_.id 
join tipo_producto tipoproduc7_ on poliza0_.tipo_producto=tipoproduc7_.id 
join compania compania8_ on poliza0_.compania=compania8_.id 
join cliente cliente9_ on poliza0_.cliente=cliente9_.id 
left join vendedor vendedor10_ on poliza0_.vendedor=vendedor10_.id

As you can see in the first part I have an inner join from Vendedor which makes the query wrong.

How should I create my query to get the expected result?

javamysqlspring-bootjpaspring-data-jpa


Solution 1:[1]

If I read correctly, you are defining your own query. So, you can edit the JOIN in your query and write LEFT JOIN instead. Am I right on your intentions?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mario Biagioni

Related Questions