CCNA, CCNP, MCSA, CCNA Final Exam, All Answer Test Module With 100/100

I am reading some values from a database that is horribly un-normalized (which I can't control). The call retrieves announcements for university departments, and if a user is in multiple departments (which is possible), then the same results are returned multiple times for these users. However, some departments might have different announcements, while some have the same. Is there a way for me to use the DISTINCT keyword in JPA on individual columns? This is what I currently have for the query: String jpql = "SELECT DISTINCT annoucement FROM Announcment announcement " + "WHERE (announcement.date <= :now AND announcement.endDate >= :now) " + "AND announcement.approved = true AND announcement.departmentId IN (:departmentIDs)"; TypedQuery<Announcement> query = entityManager.createQuery(jpql, Announcement.class); query.setParameter("now", new Date()); query.setParameter("departm

I try to convert a sql query to Criteria API without success so far. I can create two separate queries which return the values I need, but I don't know how to combine them in a single query. Here is the sql statement which works: select company.*, ticketcount.counter from company join (select company, COUNT(*) as counter from ticket where state<16 group by company) ticketcount on company.compid = ticketcount.company; This Criteria query returns the inner query results: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<intCompany> qTicket = cb.createQuery(intCompany.class); Root<Ticket> from = qTicket.from(Ticket.class); Path groupBy = from.get("company"); Predicate state = cb.notEqual(from.<State>get("state"), getStateById(16)); qTicket.select(cb.construct( intCompany.class, cb.count(from),from.<Company>get("company"))) .where(state).groupBy(groupBy); em.createQuery