'Results of group by null values are lost
I have table which has productId
and productVariantId
columns.
|id |userId|productId|productVariantId|
----------------------------------------
|1 |1 |1 |1 |
|2 |1 |1 |2 |
|3 |1 |2 |56 |
|4 |1 |3 |(null) |
|5 |2 |3 |(null) |
|6 |1 |4 |(null) |
|7 |2 |1 |1 |
...
productId
and productVariantId
are related to product
and productVariant
tables respectively (many to one). Some products has no variants, so productVariantId
might be null
. I should select all distinct products with variants. Variants of products should be considered as distinct. But, my query is not selecting rows with null valued productVariantId
s.
I have used group by pt.product, productVariant
. This works perfectly only when productVariantId
is not null.
@Query("select new com.package.name.dto.ProductTG(pt.product, pt.productVariant, concat(:language))" +
"from ProductT pt " +
"where :language=:language " +
"and (:productName = 'ALL' " +
"or (upper(pt.product.nameEng) like upper(concat('%',:productName, '%'))) " +
"or (upper(pt.product.nameRus) like upper(concat('%',:productName, '%')))) " +
"group by pt.product, pt.productVariant")
List<ProductTG> findAllProductTs(@Param("productName") String productName, @Param("language") String language);
This query selects
-------------------------------
|id|productId|productVariantId|
-------------------------------
|1 |1 |1 |
|2 |1 |2 |
|3 |2 |56 |
-------------------------------
However, I am expecting this one:
-------------------------------
|id|productId|productVariantId|
-------------------------------
|1 |1 |1 |
|2 |1 |2 |
|3 |2 |56 |
|4 |3 |(null) |
|6 |4 |(null) |
-------------------------------
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|