In my java code I have written like this. This query returning same records again and again. But in database I have only single record.
String queryString = "select e.business_event_id, e.event_name from events e left outer join subscriptions s on (e.business_event_id = s.business_event_id and s.firm_id = :firmId and s.employee_id = :employeeId) where e.object_id = :objectId and e.category_type =:categoryType and e.is_enabled =:isEnabled";
queryString += " order by e.event_name asc";
final SQLQuery query = session.createSQLQuery(queryString);
query.setParameter("firmId", firmId);
query.setParameter("employeeId", employeeId);
query.setParameter("objectId", objectId);
query.setParameter("categoryType", categoryType);
query.setParameter("isEnabled", "Y");
final List<Object[]> list = query.list();
Here it is returning same entries again and again. If anyone knows means please help me to fix this issue. Thanks in advance.
Advertisement
Answer
That’s because you are joining a parent record with multiple child rows.
Since you only need to select records from the parent entity-side, you can simply use distinct:
"select distinct e.business_event_id, e.event_name from ..."
But you should also use the hibernate.query.passDistinctThrough Hibernate query hint to avoid passing DISTINCT to the SQL statement.
Update
As @a_horse_with_no_name says, you don’t even need the left join with subscriptions since you don’t select anything from that relation and you don’t filter it either. By removing that join you won’t get any duplicates and the distinct is not required any more.