What column does DISTINCT work with in JPA and is it possible to change it?

Question

select distinct sirm.attribute
from store_item_received_material sirm
where sirm.store_item_id in (select si.id from store_item si where si.program_id = 9 and si.customer_id = 1 and si.date_processed is not null);

Answer 1

Distinct IN JPQL is most often used to select a distinct object when doing a left outer join.

This query:

SELECT DISTINCT c
FROM Customer c
LEFT JOIN FETCH c.addresses

Will select customers and all of their addresses in the addresses collection. If you don't use DISTINCT here, the same customer will repeat once for each address.

You can also use DISTINCT to select a column but it will then only return only that column:

List results = em.createQuery("SELECT DISTINCT(c.name) FROM Customer c").getResultList()

In this way, the results List will be a collection of String with customer names.