The best place to *find* answers to programming/development questions, imo, however it's the *worst* place to *ask* questions (if your first question/comment doesn't get any up-rating/response, then u can't ask anymore questions--ridiculously unrealistic), but again, a great reference for *finding* answers.

My Music (Nickleus)

20121025

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.

No comments:

Post a Comment