java ejb ql: SELECT count(*) from X where x IN (SELECT DISTINCT x FROM Y WHERE z)

when a user with the buyer role logs into the system, we want to count the distinct number of tours that have trqs (transport requests) registered for that buyer/user.

a Tour is composed of 0 to many Trqs. Trqs can only be associated with 1 Tour.

Tour table:

CREATE TABLE TOUR
(
   ID decimal(20) PRIMARY KEY NOT NULL,
...
);

Trq table:

CREATE TABLE TRQ
(
...
   BUYERID varchar2(100) NOT NULL,
   TOURID decimal(20),
...
);


the relevant TourServicesBean java code will look like this:

public int findCountByMemberId(String memberId) {
  String s = "SELECT count(t) from Tour t where t.id IN (SELECT DISTINCT tr.tourId FROM Trq tr WHERE tr.buyerId = '"+memberId+"' and tr.tourId = t.id)";
  Query q = em.createQuery(s);
  Long l = (Long) q.getSingleResult();
  return l.intValue();
}


the inner SELECT can return multiple, identical tourIds so we use DISTINCT to only get unique tourIds. then the main SELECT counts the number of unique tourids.