title: LEFT JOIN with AND operator using Criteria API
body: <p>I am trying to build dynamic queries using JPA and Criteria API but without metamodels.</p>
  
  <p>With the tables:</p>
  
  <pre><code>Foo:
  ID
  1
  2
  3
  4
  </code></pre>
  
  <hr>
  
  <pre><code>Bar: 
  ID   FooID   Number
  1    2       44
  2    2       55
  3    3       55
  </code></pre>
  
  <p>I want to retrieve all Foo entities where no matching Bar has Number 44. (Expecting Foo 1, 3, 4)</p>
  
  <p>The SQL should look something like this:</p>
  
  <pre><code>select distinct *
  from Foo foo0_
  left join Bar bar0_ on foo0_.ID=bar0_.FooID and bar0_.Number=44
  where bar0__.id is null;
  </code></pre>
  
  <p>My code looks something like this:</p>
  
  <pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;Foo&gt; cq = cb.createQuery(Foo.class);
  Root&lt;Foo&gt; fooRoot = cq.from(Foo.class);
  cq.select(fooRoot).distinct(true);
  
  List&lt;Predicate&gt; allPredicates = new ArrayList&lt;Predicate&gt;();
  List&lt;Predicate&gt; orPredicates = new ArrayList&lt;Predicate&gt;();
  List&lt;Predicate&gt; andPredicates = new ArrayList&lt;Predicate&gt;();
  andPredicates.add(cb.equal(fooRoot.join("bars", JoinType.LEFT).get("number"), 44));
  andPredicates.add(cb.isNull(fooRoot.join("bars", JoinType.LEFT).get("ID")));
  orPredicates.add(cb.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
  allPredicates.add(cb.or(orPredicates.toArray(newPredicate[orPredicates.size()])));
  
  cq.where(allPredicates.toArray(new Predicate[allPredicates.size()]));
  TypedQuery&lt;Foo&gt; query = em.createQuery(cq);
  query.getResultList();
  </code></pre>
  
  <p>But that generates this SQL:</p>
  
  <pre><code>select distinct *
  from Foo foo0_
  left outer join Bar bar1_ on foo0_.id=bar1_.FooID
  left outer join Bar bar2_ on foo0_.id=bar2_.FooID
  where bar1_.Number=44 and (bar2_.id is null);
  </code></pre>
  
  <p>and returns no Foo.</p>
  
  <p>Any ideas? Thank you!</p>