Ensuring every element in a List of Strings is present

Hello all,

I have the following method:

   public List<Certification> retrieveAllCertsHoldingMultipleCourseCodes(List<String> courseCodes) {     Query query = em.createQuery("select cert from Certification cert where cert.certStatus = 'Active' and cert.certId in (select rootCertId from CertificationStructure cs where cs.activeStatus = 'Y' and (cs.courseCode IN :courseCodes or cs.courseCode IN :eCourseCodes or cs.eNumber IN :courseCodes or cs.eNumber = :eCourseCodes))");     query.setParameter("courseCodes", courseCodes);        query.setParameter("eCourseCodes", "E" + courseCodes);                List<Certification> result = query.getResultList();        return result;    }

This returns back a list of Certifications based on looking up courseCodes (Certifications contain courses/courseCodes in the context of a Cornerstone Learning Management System [LMS]).  With the above query, I am getting back what appears to be "OR" logic - that is, if the function (from a autoComplete box on view), receives the following courseCodes:

E157123, E014123, E157111

I seem to be getting back ANY certification that contains ANY of the above.  What I am looking for, I believe, is something more like "AND" logic - I ONLY want Certifications to appear that contain ALL THREE - and if none such Certification exists, I want NO RESULTS to come back.  Try as I might, I can't seem to come up with the correct Hibernate query to make it do what I want.  Any help is greatly appreciated.

Btw: I love this forum. It reminds me of the early days of the internet.  I appreciate the aesthetic and I appreciate that you folks keep the old school forum days alive. Thank you.

This isn't JSF. It's JPA and I'll move it over there, but first...

Let me reformat that a bit so it doesn't all scroll out of sight:
final String PLSQL_CMD = "SELECT cert FROM Certification cert"    + " WHERE cert.certStatus = 'Active' "          + " AND cert.certId IN"              "  (SELECT rootCertId from CertificationStructure cs"                 + " WHERE cs.activeStatus = 'Y' AND"                     + " (cs.courseCode IN courseCodes                      + " OR"                      + " cs.courseCode IN :eCourseCodes"                      + " OR"                      + " cs.eNumber IN :courseCodes"                      + " OR"                      + " or cs.eNumber = :eCourseCodes))";

OK. You want "AND" logic. That is, ALL of the conditions are met, rather than ANY of the conditions. Well, that should be something like this, then:
final String PLSQL_CMD = "SELECT cert FROM Certification cert"    + " WHERE cert.certStatus = 'Active' "          + " AND cert.certId IN"              "  (SELECT rootCertId from CertificationStructure cs"                 + " WHERE cs.activeStatus = 'Y' AND"                     + " (cs.courseCode IN courseCodes                      + " AND"                      + " cs.courseCode IN :eCourseCodes"                      + " AND"                      + " cs.eNumber IN :courseCodes"                      + " AND"                      + " or cs.eNumber = :eCourseCodes))";
There's a rendudant set of parentheses when it's all AND clauses, but that hurts nothing. Not sure if the whole thing makes sense, but try that, at least.

I appreciate the reply Tim! However, this doesn't do what I need it to do.  Now it returns absolutely nothing back.  Somehow, I need a way to say "only bring back a Certification object if it contains ALL of the mentioned courseCodes in List<String> courseCodes; otherwise, return NOTHING".

Well, if you copied me verbatim note that the first "IN" clause was missing the ":" in front of the variable name. Sorry about that, but chopping up long lines tends to lose stuff.