Join statement on HQL

Question

I have a tables called Category and CategorySet.

CREATE TABLE CATEGORY
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(40) NOT NULL,
    description VARCHAR(255),
    uuid VARCHAR(40)
);

CREATE TABLE CATEGORY_SET
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent INT NOT NULL,
    child INT NOT NULL UNIQUE,
    FOREIGN KEY (parent) REFERENCES CATEGORY (id),
    FOREIGN KEY (child) REFERENCES CATEGORY (id)
);

Category.java

@Entity
@Table(name = "CATEGORY")
public class Category implements Serializable{
    @Id
    @Column(name = "ID")
    @GeneratedValue
    private Integer id;

    @Column(name = "CATEGORY_NAME", nullable = false, length = 40)
    private String name;

    @Column(name = "DESCRIPTION", nullable = false, length = 255)
    private String description;

    @Column(name = "UUID", nullable = false, length = 40)
    private String uuid;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "CATEGORY_SET", joinColumns = {@JoinColumn(name = "PARENT")}, inverseJoinColumns = {@JoinColumn(name = "CHILD")})
    private Collection<Category> subCategories = new LinkedHashSet<Category>();

    public Category(String name, String description, String uuid, Collection<Category> categorySets) {
        this.name = name;
        this.description = description;
        this.uuid = uuid;
        this.categorySets = categorySets;
    }
}

I want to get all root categories which means they are not child category of any.

sql to get the result is:

SELECT DISTINCT CATEGORY.id
FROM CATEGORY
  JOIN CATEGORY_SET 
    ON CATEGORY.id = CATEGORY_SET.parent
WHERE CATEGORY.id NOT IN (SELECT child
                          FROM CATEGORY_SET);

Can someone help me write HQL for this.

Answer 1

The HQL query is:

select c
from Category c
where c.id not in (select child from CategorySet)