title: JPA left join query
body: <p>Given the following two tables:</p>
  
  <pre><code>CREATE TABLE `x` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name_hash` char(32) NOT NULL,
    `access_time` bigint(20) unsigned NOT NULL,
    `name` varchar(1024) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_hash` (`name_hash`),
    KEY `access_time` (`access_time`),
    CONSTRAINT `x_ibfk_1` FOREIGN KEY (`access_time`) REFERENCES `update_time` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  CREATE TABLE `y` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `x` bigint(20) unsigned NOT NULL,
    `update_time` bigint(20) unsigned NOT NULL,
    `reason` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `x` (`x`,`update_time`),
    KEY `reason` (`reason`),
    KEY `update_time` (`update_time`),
    CONSTRAINT `y_ibfk_1` FOREIGN KEY (`reason`) REFERENCES `reason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `y_ibfk_2` FOREIGN KEY (`x`) REFERENCES `x` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `y_ibfk_3` FOREIGN KEY (`update_time`) REFERENCES `update_time` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>I used NetBeans to create the following JPA classes (X and Y are not the real names, think I did all of the required changes):</p>
  
  <pre><code>@Entity
  @Table(name = "X", catalog = "topiclymobile", schema = "", uniqueConstraints = {
      @UniqueConstraint(columnNames = {"name_hash"})})
  @NamedQueries({
      @NamedQuery(name = "X.findAll", query = "SELECT t FROM X t"),
      @NamedQuery(name = "X.findById", query = "SELECT t FROM X t WHERE t.id = :id"),
      @NamedQuery(name = "X.findByNameHash", query = "SELECT t FROM X t WHERE t.nameHash = :nameHash"),
      @NamedQuery(name = "X.findByName", query = "SELECT t FROM X t WHERE t.name = :name")})
  public class X implements Serializable 
  {
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Basic(optional = false)
      @Column(name = "id", nullable = false)
      private Long id;
  
      @Basic(optional = false)
      @Column(name = "name_hash", nullable = false, length = 32)
      private String nameHash;
  
      @Basic(optional = false)
      @Column(name = "name", nullable = false, length = 1024)
      private String name;
  
      @JoinColumn(name = "access_time", referencedColumnName = "id", nullable = false)
      @ManyToOne(optional = false)
      private UpdateTime accessTime;
  
      @OneToMany(cascade = CascadeType.ALL, mappedBy = "X")
      private List&lt;Y&gt; YList;
  
      public X() {
      }
  
      public X(Long id) {
          this.id = id;
      }
  
      public X(Long id, String nameHash, String name) {
          this.id = id;
          this.nameHash = nameHash;
          this.name = name;
      }
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      public String getNameHash() {
          return nameHash;
      }
  
      public void setNameHash(String nameHash) {
          this.nameHash = nameHash;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public UpdateTime getAccessTime() {
          return accessTime;
      }
  
      public void setAccessTime(UpdateTime accessTime) {
          this.accessTime = accessTime;
      }
  
      public List&lt;Y&gt; getYList() {
          return YList;
      }
  
      public void setYList(List&lt;Y&gt; YList) {
          this.YList = YList;
      }
  
      @Override
      public int hashCode() {
          int hash = 5;
          hash = 89 * hash + (this.nameHash != null ? this.nameHash.hashCode() : 0);
          return hash;
      }
  
      @Override
      public boolean equals(Object obj) {
          if (obj == null) {
              return false;
          }
          if (getClass() != obj.getClass()) {
              return false;
          }
          final X other = (X) obj;
          if ((this.nameHash == null) ? (other.nameHash != null) : !this.nameHash.equals(other.nameHash)) {
              return false;
          }
          return true;
      }
  }
  @Entity
  @Table(name = "Y", catalog = "topiclymobile", schema = "", uniqueConstraints = {
      @UniqueConstraint(columnNames = {"X", "update_time"})})
  @NamedQueries({
      @NamedQuery(name = "Y.findAll", query = "SELECT t FROM Y t"),
      @NamedQuery(name = "Y.findById", query = "SELECT t FROM Y t WHERE t.id = :id")})
  public class Y implements Serializable 
  {
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Basic(optional = false)
      @Column(name = "id", nullable = false)
      private Long id;
  
      @JoinColumn(name = "reason", referencedColumnName = "id", nullable = false)
      @ManyToOne(optional = false)
      private Reason reason;
  
      @JoinColumn(name = "X", referencedColumnName = "id", nullable = false)
      @ManyToOne(optional = false)
      private X X;
  
      @JoinColumn(name = "update_time", referencedColumnName = "id", nullable = false)
      @ManyToOne(optional = false)
      private UpdateTime updateTime;
  
      public Y() {
      }
  
      public Y(Long id) {
          this.id = id;
      }
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      public Reason getReason() {
          return reason;
      }
  
      public void setReason(Reason reason) {
          this.reason = reason;
      }
  
      public X getX() {
          return X;
      }
  
      public void setX(X X) {
          this.X = X;
      }
  
      public UpdateTime getUpdateTime() {
          return updateTime;
      }
  
      public void setUpdateTime(UpdateTime updateTime) {
          this.updateTime = updateTime;
      }
  
      @Override
      public int hashCode() {
          int hash = 7;
          hash = 13 * hash + (this.X != null ? this.X.hashCode() : 0);
          hash = 13 * hash + (this.updateTime != null ? this.updateTime.hashCode() : 0);
          return hash;
      }
  
      @Override
      public boolean equals(Object obj) {
          if (obj == null) {
              return false;
          }
          if (getClass() != obj.getClass()) {
              return false;
          }
          final Y other = (Y) obj;
          if (this.X != other.X &amp;&amp; (this.X == null || !this.X.equals(other.X))) {
              return false;
          }
          if (this.updateTime != other.updateTime &amp;&amp; (this.updateTime == null || !this.updateTime.equals(other.updateTime))) {
              return false;
          }
          return true;
      }
  }
  </code></pre>
  
  <p>What I am after is all of the cases that "x" does not have a corresponding "y" for a given time (access_time and update_time are the same thing).</p>
  
  <p>This SQL query works, I just cannot seem to translate it into an JPA query:</p>
  
  <pre><code>SELECT t.id FROM x t LEFT JOIN y r ON t.id = r.x WHERE r.x IS NULL AND t.access_time = 1
  </code></pre>